Here's the scenario:

I am trying to compile a bill of materials for 2 similar yet different
products. The worksheet for product 1 is complete, and to save time
on the worksheet for product 2, i would like excel to pull the part #
and description from the first worksheet if a third number is common
between the two products since they share many common parts. The
location of the third number is not necessarily common between the two
sheets and not all of them have a match. I can get as far as pulling
the correct address from the other worksheet if one exists using
=ADDRESS((N(MATCH(D7,'SHEET1!D:D,0))),4,4), but this address won't
nest in a CELL("CONTENTS"....) function. Any ideas?

Thanks,
Chris