Lookup & Insertion Of Data

[Mike]

I have two worksheets. On the first there is a column listing Manufacturers
Part Numbers. The listing is textual content and is OK to have repeating
items in the list.

The second worksheet contains a 3 column table of Manufacturers Part Numbers
(same content format as worksheet one), Manufacturer Name, and our In-House
Part Number.

I need to check the Manufacturers Part Numbers on worksheet one against
worksheet two and if a match occurs I need to copy Manufacturers Name & In
House Part Number data from worksheet two back to worksheet one in the
corresponding / adjacent two columns.

 

[Tom Hayakawa]

Hi Mike,

If you really mean worksheets in one workbook, try this:

Assume the following:

Sheet1 has the single column of Mfr Part #'s in Column A, starting at
Sheet1!A2.

Sheet2 has the three columns of Mfr Part #'s, Mfr Names, In-house Part #'s
and they are in the range Sheet2!A2:C100.

In cell Sheet1!B2 enter:
=IF(ISNA(VLOOKUP($A2,Sheet2!$A$2:$C$100,2,FALSE)),"",VLOOKUP($A2,Sheet2!$A$2:$C$100,2,FALSE))

In cell Sheet1!C2 enter:
=IF(ISNA(VLOOKUP($A2,Sheet2!$A$2:$C$100,3,FALSE)),"",VLOOKUP($A2,Sheet2!$A$2:$C$100,3,FALSE))

Copy these two cells down the length of the list.

Of course, this also assumes another pre-condition:
The Mfr Part # is tied to only one other In-house Part #.

This should get you an answer - whether it's the right one or not, you're
going to have to try it and see if it works in all cases. But if the
pre-condition is false, the formulas will not give you accurate results. If
that's the case, you might investigate filters, pivot tables, or even the
SUMPRODUCT function.

FWIW, HTH.

 

[Mike]

Thanks Tom,

I was able to use what you provided along with what I had already figures
out. I ended up with;

=IF(ISNA(VLOOKUP(B1,'Library Cross
Reference'!B:C,2,FALSE)),"",VLOOKUP(B1,'Library Cross Reference'!B:C,2,))

I appears to work - I'm still working through some test cases.

Thanks again!