How to use the Excel IFNA function

Summary

The Excel IFNA function is a simple way to trap and handle #N/A errors specifically without catching other errors. The IFNA function returns a custom result when a formula generates the #N/A error, and a normal result when no error is detected.

Purpose

Trap and handle #N/A errors

Return value

The value supplied for #N/A errors

Syntax

=IFNA (value, value_if_na)

Arguments

value - The value, reference, or formula to check for an error.
value_if_na - The value to return if #N/A error is found.

Version

Excel 2013

Usage notes

The IFNA function is a simple way to trap and handle #N/A errors specifically without catching other errors. You can use the IFNA function to trap and handle #N/A errors that may occur in formulas that perform lookups with MATCH, VLOOKUP, HLOOKUP, XLOOKUP, etc. The IFNA function returns a custom result when a formula generates the #N/A error, and a normal result when no error is detected. IFNA will only handle #N/A errors –other errors will still display.

Example

The IFNA function can be used to trap #N/A errors that may occur with the VLOOKUP function. In the example shown, the formula in F5, copied down, is:

=IFNA(VLOOKUP(E5,xtable,2,0),"Not found")

where xtable is the named range B5:C12. When the lookup value in column E is found in xtable, VLOOKUP returns the exchange range normally. When the lookup value is not found, VLOOKUP returns #N/A, and IFNA catches this error and returns "Not found".

IFERROR or IFNA?

The IFERROR function is a useful function, but it is a blunt instrument, since it will trap many kinds of errors. For example, if there's a typo in a formula, Excel may return the #NAME? error, but IFERROR will suppress the error and return the alternative result. This can obscure an important problem. In many cases, it makes more sense to use the IFNA function, which only traps the #N/A error.

Notes

If value is empty, it is evaluated as an empty string ("") and not an error.
If value_if_na is supplied as an empty string (""), no message is displayed when an error is detected.

Related functions

Excel ISERROR Function

The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or...

Excel IFERROR Function

The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

Excel ISNA Function

The Excel ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. You can use the ISNA function with the IF function test for an error and display a friendly message when it appears...