#N/A! Index & Match

  • I have a formula that performs an INDEX | MATCH on a table of data based on two input cells. If the inputs are outside the bounds of the data table the result is blank.


    The data tables size and contents are static. The inputs used to perform the INDEX and MATCH are [dr]*[/dr].


    =INDEX(MatrixUnified,MATCH(FICO,FICOArrayUnified),MATCH(Internal,InternalArrayUnified))*100


    Could this be easily solved by introducing and IF statement that if the resulting INDEX and MATCH is outside the bounds of the data table the result would appear as '0' (zero)?


    Thanks in advance.

  • Re: Index And Match


    You can do that; however, it involves Excel potentially performing the calculation twice. This isn't a problem unless you have a significant number of these calculations, in which case your spreadsheet's recalculation time will lengthen.

    =IF(ISERROR(INDEX(MatrixUnified,MATCH(FICO,FICOArrayUnified),MATCH(Internal,InternalArrayUnified))),0,INDEX(MatrixUnified,MATCH(FICO,FICOArrayUnified),MATCH(Internal,InternalArrayUnified))*100)

    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Index And Match


    Excellent! I wasn't sure if ISERROR would be the correct solution, but it didn't seem to me that it would be because the result I was getting in my sheet wasn't NA. I have other ISERROR formulas in my sheet to handle this type of result, but didn't know if it would work in the INDEX | MATCH scenario.


    Thanks, Batman!. This is exactly what I was looking for. Thanks for the info about the double calculations -- good information to know. Luckily, this calculation is only done in one result cell.

  • Re: Index And Match


    Dave,

    I wasn't quite sure on that one either, because if either the row or column index doesn't fall within the bounds of the table the formula returns a #REF error.

    EgoProwler,

    For info, if you are doing a standard VLookup that can't find what it's looking for VLookup will return a #N/A error. In these circumstances you should use ISNA instead of ISERROR. ISNA checks for the formula answer being #N/A and if it is actions accordingly. ISERROR checks for any error result, which could be because you have coded the formula incorrectly, and is therefore best avoided unless you have no alternative.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Index And Match


    Quote from Dave Hawley

    I don't get it, nothing new there :) If the result is not found, how does the formula you show return a blank??


    Was my mistake. The cell does return #N/A when the result is outside the bounds of the table.


    Quote from Batman

    For info, if you are doing a standard VLookup that can't find what it's looking for VLookup will return a #N/A error. In these circumstances you should use ISNA instead of ISERROR. ISNA checks for the formula answer being #N/A and if it is actions accordingly. ISERROR checks for any error result, which could be because you have coded the formula incorrectly, and is therefore best avoided unless you have no alternative.


    Excellent information. Thanks! You guys are the best. I learn so much every time I come here.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!