New Contributor

# Vlookup with multiple criteria

Scenario:

IF(vlookup(a2,sheet1!X:X,1,false)=a2

AND(vlookup(b2,sheet1!Y:Y,false)=b2

THEN(vlookup(b2,sheet1!X:Z,3,false)

IF NOT, blank

In other words, look up A & B on sheet1, look at sheet 2 for the same two items and tell me what in the next column. If there's no match, then blank.

6 Replies

# Re: Vlookup with multiple criteria

=IF(AND(NOT(ISNA(VLOOKUP(E1&F1,A2:A24&B2:B24,1,FALSE))),NOT(ISNA(VLOOKUP(E1&F1,sheet2!A2:A24&sheet2!B2:B24,1,FALSE)))),VLOOKUP(sheet1!E1&sheet1!F1,CHOOSE({1,2},sheet2!A2:A24&sheet2!B2:B24,sheet2!C2:C24),2,0),"")

Enter above formula as arrayformula with ctrl+shift+enter.

Enter lookup values in E1 and F1.

Is this what you are looking for?

# Re: Vlookup with multiple criteria

Sorry, I'm having trouble translating columns. I've attached a sample sheet.

I'm trying to match the Est and ACCUM from one sheet to the other.

# Re: Vlookup with multiple criteria

Excel 365 Pro Plus with Power Pivot and Power Query.

Lookup with three criteria.

No formulas, just GUIs.

There are oodles of other ways, Share file.

https://www.mediafire.com/file/95nq78p6x063a5o/10_29_21b.xlsx/file

https://www.mediafire.com/file/096nghzvyh6fqc9/10_29_21b.pdf/file

# Re: Vlookup with multiple criteria

And if multiple matches:

# Re: Vlookup with multiple criteria

=IFERROR(VLOOKUP(A2&B2,CHOOSE({1,2},Sheet1!\$A\$2:\$A\$1218&Sheet1!\$B\$2:\$B\$1218,Sheet1!\$C\$2:\$C\$1218&" "&Sheet1!\$D\$2:\$D\$1218),2,0),"")

Enter above formula in sheet2 as arrayformula with ctrl+shift+enter and then copy down.

Formula returns results of column C and D of sheet1 if job# and item are found in sheet1.  If there is no match, an empty cell is returned.

# Re: Vlookup with multiple criteria

Now with OPs real database.

As mentioned by others, there are up to 16 matches for each set.

Needed massive cleanup.

With Power Pivot and Power Query.

No formulas, just GUI's and menu picks.

https://www.mediafire.com/file/d626zqt8pblxm00/10_30_21.xlsx/file

https://www.mediafire.com/file/qlui82t8ipduugv/10_30_21.pdf/file