Forum Discussion

harlequintp's avatar
harlequintp
Copper Contributor
Apr 12, 2019
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 ...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 12, 2019

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.

harlequintp's avatar
harlequintp
Copper Contributor
Apr 12, 2019
That makes sense, I was just looking up within the function what the multiplication meant, thank you very much for the help and explanation.
  • harlequintp's avatar
    harlequintp
    Copper Contributor
    Apr 15, 2019

    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.

  • harlequintp's avatar
    harlequintp
    Copper Contributor
    Apr 15, 2019

    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.

Resources