Vlookup with multiple matches

I have a spreadsheet where I need to match to another spreadsheet based off of two columns. Can I do a vlookup to match 2 columns and bring back the value of the 3rd? There will be multiples, but only one that will match both exactly. Like product numbers listed more than once and invoices more than once but there will only be one instance where they both match my other sheet and I would need to bring back the GL code on that.

You can't use VLOOKUP like that but you can use a different formula, e.g. to find the first row where the criteria is met in columns A, B and C and then return the value from D

FYI its an array

=INDEX(D1:D100,MATCH(1,(A1:A100="x")*(B1:B100="y")*(C1:C100="z"),0))

confirmed with CTRL+SHIFT+ENTER

Good idea! you can have the helper column be a concatenate of the two columns so you could use a single V-Lookup to locate the value.

Another one....

Based on Tony Valko table listed the above


G2: =LOOKUP(2,1/(A2:A9=E2)/(B2:B9=F2),C2:C9)


Normally Enter

sample.xls

Thanks for all the answers, here is a quick scaled down sample of what I will need to be able to do. I will have to fill in the GL code on a second spreadsheet based on the Tracking Number & Invoice. There will be a lot of data in the real file.

Originally Posted by FDibbins

Hi and welcome to the forum

An alternative to the array offered above would be to add a helper column and combine the other 2 cells/columns. This can be hidden, and if you add it to the left of your data, you can still use the standard vlookup(). If you add the helper column to the right of the data, you will need to use an index/match formula.

If you upload a sample workbook, I can show you how to do this.

Array formulas are fine, but can start to slow files down if you have a LOT of data that they are working on

Thank you so much, worked perfectly on my test sheet. Let's hope I can get it to work on the big one!!

Originally Posted by Tony Valko

Enter this array formula** in H2 and copy down:

=INDEX(C$2:C$9,MATCH(G2,IF(A$2:A$9=F2,B$2:B$9),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.