Index Match with multiple criteria

[harlequintp]

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 

[#2 · Accepted solution]

@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)))

[#3]

Hi Sergei, that is exactly what I wanted, thank you. I will now spend some time reading though it and understanding it.

[#4]

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

[#5]

That makes sense, I was just looking up within the function what the multiplication meant, thank you very much for the help and explanation.

[#6]

Hi Sergie,

Not sure if you will see this.  You helped me last week on an Index/Match formula.  I've just been sent the data that I need to apply the formula to and there is a small difference.  In the ID column there I used the example of 456A, you have used the ISNUMERIC funtion to strip out the A to then do a check against the numbers.  In the data I have been sent there can be something like 1ASN3Z in the G column and in my Ref table column A the letters SN.  In this case I would be looking for the SN in 1ASN3Z, the letter code and QTY will only every appear as unique so it will be still a 1 to 1 match.  I am thinking it is just a case of removing the ISNUMERIC function but I am sure it wont be as easy as that.  Again my apologies that I wasn't sent all the possible examples of this.  I have re-uploaded an example to show this.

[#7]

Hi Sergie,

Not sure if you will see this.  You helped me last week on an Index/Match formula.  I've just been sent the data that I need to apply the formula to and there is a small difference.  In the ID column there I used the example of 456A, you have used the ISNUMERIC funtion to strip out the A to then do a check against the numbers.  In the data I have been sent there can be something like 1ASN3Z in the G column and in my Ref table column A the letters SN.  In this case I would be looking for the SN in 1ASN3Z, the letter code and QTY will only every appear as unique so it will be still a 1 to 1 match.  I am thinking it is just a case of removing the ISNUMERIC function but I am sure it wont be as easy as that.  Again my apologies that I wasn't sent all the possible examples of this.  I have re-uploaded an example to show this.