# 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

`Application.Worksheetfunction.ISNA(A2)`

#### 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

Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist