Forum Discussion
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 closest match, then check that the Qty is correct, if so add in the Sal value. If Qty is 0 then Sal will be 0 no matter what the ID value says.
In my attachment I have put an example
In A1 to D8 is my reference table.
G1 to I10 is where I am trying to update values.
In the column I is my Index / Match, I3 to I5 make an exact match on the ID field and then show the correct Sal from the reference table. I6 to I9 have no exact Match and currently show "OH" just for reference.
In G6, this is very close to what is shown in A6 and as the Qty matches between the Reference records and the update records then I6 should show 50. The same rules will apply to G7 and G8.
If the Value in A appears anywhere in the field of column G, examples A123, 123A, CR123, etc and the Qty matches, then its a match.
I hope I have put enough details in but if anything else is needed please ask.
Thanks
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)))
6 Replies
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.
