VLOOKUP on Matching Multiple Criteria

[DocAElstein]

I took a quick look in the internet. A quick Google with a search of “VLOOKUP on Matching Multiple Criteria“ brings you a lot of infomation and examples…
To solve the problem with a single formula would be very difficult.
There are many similar problems solved on the internet which use a helper column.
A search of the internet will give you many examples. Here just a few
https://trumpexcel.com/vlookup-with-multiple-criteria/
https://exceljet.net/formula/vlookup...tiple-criteria

Example in uploaded file, VLookUpMultipleCriteria.xlsx
Your formula in row 2 to be copied down would be:
_____ Workbook: VLookUpMultipleCriteria.xlsx ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE))	=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE))

Worksheet: Result

---

There is also some information on the internet to solve the problem without a helper column. But the solution does not use VLookUp.
It ends up to be a fucking long formula when applied to your data: ( It is a CSE formula! ) In row 2 to be copied down:
_____ Workbook: FuckingLongFormulaMultipleCriteria.xlsx ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IF(ISERROR(INDEX(Export!$E$2:$E$8,MATCH(1,(ResultIndex!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1)),"",INDEX(Export!$E$2:$E$8,MATCH(1,(ResultIndex!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1))	=IF(ISERROR(INDEX(Export!$F$2:$F$8,MATCH(1,(ResultIndex!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1)),"",INDEX(Export!$F$2:$F$8,MATCH(1,(ResultIndex!A2=Export!$A$2:$A$8)*(ResultIndex!B2=Export!$B$2:$B$8)*(ResultIndex!C2=Export!$C$2:$C$8),0),1))

Worksheet: ResultIndex

Example in uploaded File, FuckingLongFormulaMultipleCriteria.xlsx

If you have a lot of real data, then such a fucking long formula is probably a bad idea.
A better solutions is probably Sandy666’s , or possibly a VBA solution


Alan

[sandy666]

A better solutions is probably Sandy666's , or possibly a VBA solution

so do that

[msiyab]

Example in uploaded file, VLookUpMultipleCriteria.xlsx
Your formula in row 2 to be copied down would be:
_____ Workbook: VLookUpMultipleCriteria.xlsx ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,2,FALSE))	=IF(ISERROR(VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE)),"",VLOOKUP(A2&"|"&B2&"|"&C2,Export!$D$2:$F$8,3,FALSE))

Worksheet: Result

---

Alan

I used the Vlookup formula you provided here and it worked well. Will try out in our reports during the month end and will provide a feedback then.