Check Excel #N/A using ISNA Function

This Excel tutorial explains how to check Excel #N/A cells using worksheet formula ISNA and VBA Function.

You may also want to read:

Excel IFERROR Function

Cause of Excel #N/A

#N/A is usually caused by lookup related functions where a value cannot be found in lookup table. Lookup Functions include Vlookup, Match, HLookup, Lookup.

Another common cause is that when you use an Add-In formula, and then send the file to someone who doesn’t have that Add-In. The formula was fine at the time of open the workbook but the formula will turn #N/A once formula is triggered to recalculate.

Check Excel #N/A using worksheet formula

If you want to prevent #N/A in a template with Vlookup formula, you can use IFERROR Function, which captures #N/A as well as other errors. For example

=IFERROR(VLOOKUP(D3,$A$3:$B$7,2,0),”Not found”)

If you try to check if the Cell contains exactly #N/A, use ISNA Function. For example, if A2 contains #N/A then

=ISNA(A2) returns TRUE

Check Excel #N/A using VBA

Excel #N/A is extremely annoying in VBA because it probably causes all the subsequent VBA code to fail.

Method 1

To capture Excel #N/A in VBA, the best way is to use the worksheet function ISNA.

For example


Method 2

Alternatively, you can also use Range.Text

Range("A2").Text = "#N/A"

Range.Text is what the screen displays, do not use Range.Value because it gets the underlying value.

Method 3

Finally, you can also use  On Error Resume Next to skip the error.

Outbound References




Leave a Reply

Your email address will not be published.