ISNA

Hi Anjana

Thank you for your question.

ISNA() tests the results of a function such as VLOOKUP(). For example if the input for the lookup is not found the function returns a value of #NA. This causes problems if the results are included in a SUM() range as they can't be totalled.

ISNA can be combined with IF() statements to replace the #NA message with something more useful such as zero.

=IF(ISNA(VLOOKUP(E6,B7:C11,2,FALSE)),0,VLOOKUP(E6,B7:C11,2,FALSE))


in this example the IF function's logical test is the VLOOKUP withn an ISNA function. The result is either TRUE (the lookup failed to find a match and so would show NA) or FALSE (lookup would show a returned value from the list). Based on this test the value if true is zero, the value if false is the Vlookup again. This time it's not in an ISNA function so it returns the result of the lookup.

I hope this helps. Do let us know if you have any further questions.

Kind regards,
Andrew