VLOOKUP returns a incorrect match

[nander]

In the attached spreadsheet I'm using VLOOKUP to create a cross reference between worksheets JS and ITEM. If you will look at the ITEM worksheet cell reference H13 & H14. The correct value for H13 should be AMC, not 729. Is there a way to use the value in the Class column and Item ID column in combination to get the value AMC? Would MATCH & INDEX work? I'm not familiar with Match & Index. I'd appreciate some help here. I've got 15,404 records to evaluate this way. Thanks

[BigBas]

I'm confused by your request.

Looking at the JS table, the VLOOKUP for Part Number 7000 SHOULD return 729 (that is in Column 3 of your table). From where should excel get AMC?

[nander]

I've updated the attached file. You will notice in cells H13-H15 the result is AMC. AMC is correct for cell H13 but not for H14 or H15. See what I have in this small example is 3 separate Vendors but these 3 vendors have the same Vendor Part number. In a section below the main group of examples,(same worksheet) I have the 3 items with their correct vendors.

[JustinBram]

The problem is the lookup is match the first 7000 value on the JS worksheet, which is column 15. your products need to have unique manufacture part numbers for this to work, at the moment you have 3 items with 7000 as their manuf part number.

just finish my dinner hour so i will have to have another look either tonight or tomorrow.

[NBVC]

I've updated the attached file. You will notice in cells H13-H15 the result is AMC. AMC is correct for cell H13 but not for H14 or H15. See what I have in this small example is 3 separate Vendors but these 3 vendors have the same Vendor Part number. In a section below the main group of examples,(same worksheet) I have the 3 items with their correct vendors.

It seems like the information your pulling is one of the categories you want to match up... (i.e. Class in one sheet is the same thing as Vendor Code in the other)

If so try this in H2:

=IF(ISNA(VLOOKUP(A2,JS!$A$2:$D$21,3,FALSE))," ",INDEX(JS!$C$2:$C$21,MATCH(1,(JS!$A$2:$A$21=A2)*(JS!$C$2:$C$21=D2),0)))

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER... you'll see {} brackets appear around the formula....then copy down the formula.

If you make any revisions you will have to reconfirm with the CSE key combo.

[fletch396]

Hi
try this hope it helps





Fletch

[nander]

Fletch the file was corrupted. Could you resend?

[NBVC]

Did you try my suggestion? Did it not work?

[nander]

Yes I started to do that after I got the corrupted file. I'll let you know

[nander]

Yes, I tried it and it works except I get a #NA for one of the values. I've attached the updated file. Can you explain to me what I need to do to correct that, thank you.