However,  #N/A errors can also be caused by extra space characters, misspellings, or an incomplete lookup table. The functions mostly commonly affected by the #N/A error are classic lookup functions, including VLOOKUP, HLOOKUP, LOOKUP, and MATCH. The best way to prevent #N/A errors is to make sure lookup values and lookup tables are correct and complete. If you see an unexpected #N/A error, check the following first: Note: if you get an incorrect result, when you should see a #N/A error, make sure you have exact matching configured correctly. Approximate match mode will happily return all kinds of results that are totally incorrect :)

Trapping the #N/A error with IFERROR

One option for trapping the #N/A error is the IFERROR function. IFERROR can gracefully catch any error and return an alternative result . In the example shown, the #N/A error appears in cell F5 because “ice cream” does not exist in the lookup table, which is the named range “data” (B5:C9). To handle this error, the IFERROR function is wrapped around the VLOOKUP formula like this: If the VLOOKUP function returns an error, the IFERROR function “catches” that error and returns “Not found”.

Trapping the #N/A error with IFNA

The IFNA function can also trap and handle #N/A errors specifically. The usage syntax is the same as with IFERROR: The advantage of the IFNA function is that it is more surgical, targeting just #N/A errors. The IFERROR function, on the other hand, will catch any error. For example, even if you spell VLOOKUP incorrectly, IFERROR will return “Not found”.

No message

If you don’t want to display any message when you trap an #N/A error (i.e. you want to display a blank cell), you can use an empty string ("") like this:

INDEX and MATCH

The MATCH function also returns #N/A when a value is not found. If you are using INDEX and MATCH together, you can trap the #N/A error in the same way. Based on the example above, the formula in F5 would be: Read more about INDEX and MATCH.

Forcing the #N/A error

If you want to force the #N/A error on a worksheet, you can use the NA function. For example, display #N/A in a cell when A1 equals zero, you can use a formula like this:

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.