Use the IFNA function in Excel to replace a #N/A error with a friendly message. The IFNA function only catches #N/A errors.

1. For example, Excel returns the #N/A error when the VLOOKUP function cannot find a match.

#N/A error

2. Use the IFNA function to replace the #N/A error with a friendly message.

IFNA function in Excel

3. If the VLOOKUP function does not evaluate to a #N/A error, the IFNA function simply returns the result of the VLOOKUP function.

VLOOKUP result

The IFNA function was introduced in Excel 2013. If you're using Excel 2010 or Excel 2007, combine IF and ISNA (step 5) or use IFERROR (step 6).

4. First, the ISNA function checks whether a value is #N/A, and returns TRUE or FALSE.

ISNA function

5. Combine IF and ISNA.


6. The previous formula is quite long. You can also use the IFERROR function in Excel.

IFERROR function

Note: the IFERROR function catches other errors as well. For example, the #NAME? error if you accidentally misspell the word VLOOKUP.