Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

isna

ResolvedVersion 2007

Anjana has attended:
Excel Intermediate course

ISNA

how do i use an isna?

RE: 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

Wed 9 Dec 2009: Automatically marked as resolved.

Excel tip:

Enter formulae into multiple cells

If a formula is to be used in a series of cells, select the cells first. Now type in your formula and hold the Ctrl key while you press Enter. This enters the formula in each selected cell.

Just be careful with your absolute and relative cell references.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.