Lookup & Insertion Of Data
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. |
Lookup & Insertion Of Data
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" wrote: 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. |
Lookup & Insertion Of Data
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! "Tom Hayakawa" wrote: 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" wrote: 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. |
All times are GMT +1. The time now is 04:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com