Vlookup with multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2901641%22%20slang%3D%22en-US%22%3EVlookup%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2901641%22%20slang%3D%22en-US%22%3E%3CP%3EScenario%3A%3C%2FP%3E%3CP%3EIF(vlookup(a2%2Csheet1!X%3AX%2C1%2Cfalse)%3Da2%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EAND(vlookup(b2%2Csheet1!Y%3AY%2Cfalse)%3Db2%3C%2FP%3E%3CP%3ETHEN(vlookup(b2%2Csheet1!X%3AZ%2C3%2Cfalse)%3C%2FP%3E%3CP%3EIF%20NOT%2C%20blank%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20look%20up%20A%20%26amp%3B%20B%20on%20sheet1%2C%20look%20at%20sheet%202%20for%20the%20same%20two%20items%20and%20tell%20me%20what%20in%20the%20next%20column.%20If%20there's%20no%20match%2C%20then%20blank.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2901641%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2901697%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2901697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1200123%22%20target%3D%22_blank%22%3E%40bfield66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(NOT(ISNA(VLOOKUP(E1%26amp%3BF1%2CA2%3AA24%26amp%3BB2%3AB24%2C1%2CFALSE)))%2CNOT(ISNA(VLOOKUP(E1%26amp%3BF1%2Csheet2!A2%3AA24%26amp%3Bsheet2!B2%3AB24%2C1%2CFALSE))))%2CVLOOKUP(sheet1!E1%26amp%3Bsheet1!F1%2CCHOOSE(%7B1%2C2%7D%2Csheet2!A2%3AA24%26amp%3Bsheet2!B2%3AB24%2Csheet2!C2%3AC24)%2C2%2C0)%2C%22%22)%3C%2FP%3E%3CP%3EEnter%20above%20formula%20as%20arrayformula%20with%20ctrl%2Bshift%2Benter.%3C%2FP%3E%3CP%3EEnter%20lookup%20values%20in%20E1%20and%20F1.%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2901855%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2901855%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20I'm%20having%20trouble%20translating%20columns.%20I've%20attached%20a%20sample%20sheet.%3C%2FP%3E%3CP%3EI'm%20trying%20to%20match%20the%20Est%20and%20ACCUM%20from%20one%20sheet%20to%20the%20other.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2902866%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2902866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1200123%22%20target%3D%22_blank%22%3E%40bfield66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20365%20Pro%20Plus%20with%20Power%20Pivot%20and%20Power%20Query.%3C%2FP%3E%3CP%3ELookup%20with%20three%20criteria.%3C%2FP%3E%3CP%3ENo%20formulas%2C%20just%20GUIs.%3C%2FP%3E%3CP%3EThere%20are%20oodles%20of%20other%20ways%2C%20Share%20file.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mediafire.com%2Ffile%2F95nq78p6x063a5o%2F10_29_21b.xlsx%2Ffile%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mediafire.com%2Ffile%2F95nq78p6x063a5o%2F10_29_21b.xlsx%2Ffile%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mediafire.com%2Ffile%2F096nghzvyh6fqc9%2F10_29_21b.pdf%2Ffile%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mediafire.com%2Ffile%2F096nghzvyh6fqc9%2F10_29_21b.pdf%2Ffile%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2903087%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2903087%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1200123%22%20target%3D%22_blank%22%3E%40bfield66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20if%20multiple%20matches%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1635572793616.png%22%20style%3D%22width%3A%20681px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321772i69D59B9273003F03%2Fimage-dimensions%2F681x429%3Fv%3Dv2%22%20width%3D%22681%22%20height%3D%22429%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1635572793616.png%22%20alt%3D%22Yea_So_0-1635572793616.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@bfield66 

=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? 

 

@Quadruple_Pawn 

 

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.

@bfield66 

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

 

@bfield66 

And if multiple matches:

Yea_So_0-1635572793616.png

 

@bfield66 

=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.

@Herbert_Seidenberg 

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