Forum Discussion
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.
4 Replies
=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?
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.
=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.
