Forum Discussion
Solved
Index Match with multiple criteria
Hi, I am trying to do multiple checks in Excel 2010. I want to look up a value and put it in a corresponding column. When there is no match I want it to check the first reference table for the ...
harlequintp , that could be
=INDEX($C$2:$C$9,IF(ISNUMBER(G2),MATCH(G2,$A$2:$A$9,0),MATCH(1,INDEX((H2=$D$2:$D$9)*ISNUMBER(SEARCH(A2,$G$2:$G$9)),0 ),0)))
harlequintp , that could be
=INDEX($C$2:$C$9,IF(ISNUMBER(G2),MATCH(G2,$A$2:$A$9,0),MATCH(1,INDEX((H2=$D$2:$D$9)*ISNUMBER(SEARCH(A2,$G$2:$G$9)),0 ),0)))
- Hi Sergei, that is exactly what I wanted, thank you. I will now spend some time reading though it and understanding it.
harlequintp , here you first check if the value is number or not. If it's number when simple MATCH. If not, when within second MATCH the INDEX generates array of 1 or 0 (which is actually TRUE and FALSE) for your two criteria, multiplication means AND condition. MATCH takes position of 1 (aka TRUE) in that array, other words position of the row for which all your criteria match.
- That makes sense, I was just looking up within the function what the multiplication meant, thank you very much for the help and explanation.
