ID EN
…
#Logical

IFNA

Excel Functions

Trap and handle #N/A errors

Syntax

EXCEL 
=IFNA(value, value_if_na)

Arguments

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

Return Value

The value supplied for #N/A errors

Details

The IFNA function is designed to manage #N/A errors and ignore other errors. When a function returns an #N/A, it typically indicates that a value is not available or not found. In many cases, an #N/A error is useful information because it tells you the formula is not able to find a value. However, the #N/A error can make users uncomfortable, because it might make it seem that there something is wrong with the worksheet. The IFNA function gives you a simple way to "catch" an #N/A error and provide another more user-friendly result. Unlike the more general IFERROR function, the IFNA function will only trap #N/A errors specifically; other errors will still be displayed. This is useful because it means the IFNA function won't accidentally hide another more serious error. You can use the IFNA f

Examples

Example

For example, in the worksheet shown, we are using VLOOKUP to find an item's price in the range B5:C16. The formula in F5, copied down, looks like this

EXCEL 
=VLOOKUP(E5,$B$5:$C$16,2,FALSE)
Example

To use the IFNA function to trap #N/A errors, embed the original formula inside IFNA as the first argument. In this case, we start off with the IFNA f

EXCEL 
=IFNA(
Example

Then we paste in the original formula like so:

EXCEL 
=IFNA(VLOOKUP(H5,$B$5:$C$16,2,FALSE),
Example

Next, provide an alternative result as the second argument. In the worksheet shown, we provide an empty string ("") so that the #N/A error is effectiv

EXCEL 
=IFNA(VLOOKUP(H5,$B$5:$C$16,2,FALSE),"")
Example

Notice that the result in cells I5, I6, and I7 is unaffected; VLOOKUP returns the item price as before. However, in cell I8, we now see a blank cell.

EXCEL 
=IFNA(VLOOKUP(H5,$B$5:$C$16,2,FALSE),"Not found")
IFERROR vs IFNA

Like the IFNA function, the IFERROR function is designed to manage errors. In the worksheet shown, we can use IFERROR instead of IFNA like this:

EXCEL 
=IFERROR(VLOOKUP(H8,$B$5:$C$16,2,FALSE),"")

See Also

ISERROR IFERROR ISNA