VLOOKUP on Matching Multiple Criteria

[DocAElstein]

Hi P45cal
Its interesting to see some different ways to do this, ( and comparing ways is often more interesting than feedback, if any, from the OP, Lol ) I thought something like this had probably got different ways in Excel
I see your using IFERROR in the formula offering. I almost used that. But IFERROR was introduced at Excel 2007. I have a strange habit of liking to get stuff working first in earlier versions. Typically at about Excel 2003 is where I usually start.
If I understand properly its like this: IFERROR is pseudo
__( This formula if it doesn't error , This if that formula did error )
In our case like we want it to do nothing , that is to say, leaving the cell empty, " " , if the formula errors, as that happens if it found no match. So like
__( This formula if it doesn't error , " " )
I was using the 2003 equivalent, since the ISERROR is available and returns True or False depending on if a formula errors.
So I had like
_IF( ISERROR(The formula) , then " " , Otherwise The formula )

These would be the formulas I gave in IFERROR form. ( They still need to be CSE )
_____ Workbook: NotSoLongFormulasMultipleCriteria.xlsx ( Using Excel 2007 32 bit )

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

Worksheet: ResultIndex2

I put that in the uploaded file, NotSoLongFormulasMultipleCriteria.xlsx as well as your one. Also , for completeness I put the "IF(ISERROR , " " , … version of yours. That has this sort of form
_____ Workbook: NotSoLongFormulasMultipleCriteria.xlsx ( Using Excel 2007 32 bit )

Row\Col	D	E
2	=IF(ISERROR(INDEX(Export1!D$1:D$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0))),"",INDEX(Export1!D$1:D$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0)))	=IF(ISERROR(INDEX(Export1!E$1:E$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0))),"",INDEX(Export1!E$1:E$9,MATCH($A2 & "¬" & $B2 & "¬" & $C2,Export1!$A$1:$A$9 & "¬" & Export1!$B$1:$B$9 & "¬" & Export1!$C$1:$C$9,0)))

Worksheet: P45cal

---

I expect sandy might be interested in your PQ offering. ( I still don't have a clue about any of that stuff… too much to learn in a lifetime, Lol.. maybe I will get around to learning PQ one day… )

There are probably infinite ways to do it in VBA. I will probably add one later


Alan