matching part numbers dispersed in two columns

[kam6]

Hello - I work with price lists in Excel. I want to match lists whos
main columns are part numbers. Problem is that there are part number
that do not match in between the two lists.

For example, the part numbers column in one list (I'll call it "A")ma
have the part numbers 72579, 72580, 72581, 72582, 72583. The par
numbers column in the other list (I'll call it "B") may have 72579
72580, 72582, 72583, 72584.

I want to match the numbers from list "B" to list "A". If Excel coul
just line up the matching numbers on a new column (and leaving blan
cells for unmatched part numbers) that would be great.

Each list has different information, for example list "A" has price
sorted by part number; while list "B" has UPC codes sorted by par
numbers.

My final aim is to add those UPC codes from list "B" into list "A" t
their respective part numbers.

There are thousands of these part numbers and it's very time consumin
to match them one-by-one!

Any help on this topic is appreciated, best regards

 

[jeff]

hi,

Let's say you have your "A" list in sheet1, and your "B"
list on sheet2 in cells B21:C25 (B has the part numbers,
and C has your UPC codes). if you paste this

=IF(ISERROR(VLOOKUP(A15,Sheet3!
$B$21:$C$25,1,FALSE)),"",VLOOKUP(A15,Sheet3!
$B$21:$C$25,1,FALSE))

in sheet1!c15 it should return the matching part number
in sheet2 if it exists - otherwise will be blank.

in sheet1!d15 paste this

=IF(ISERROR(VLOOKUP(A15,Sheet3!
$B$21:$C$25,2,FALSE)),"",VLOOKUP(A15,Sheet3!
$B$21:$C$25,2,FALSE))

and it'll return the corresponding UPC code (if exists);

Modify the cell refs to match your data.

jeff

 

[kam6]

Hi Jeff - thank you for your response, but the formula makes referenc
to "A15 Sheet3", and I don't have a Sheet 3 with data (I onl
positioned some data as indicated, on Sheets 1 and 2). Should "Sheet 3
in the formula be changed to "Sheet 2"? Advice is appreciated, than
you

Attached is my test spreadsheet

Carlo

Attachment filename: formula test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=57212

 

[kam6]

Hi Jeff - I tried the formulas again. IT WORKED. The Sheet3! had to b
changed to Sheet2! It organizes them flawlessly. This will help m
enormously. Thank you very much