IFNA Function

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 function to trap and handle #N/A errors that may occur in formulas that perform lookups, such as VLOOKUP, MATCH, HLOOKUP, 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. 

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:

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

Notice the formula works fine in the first three cells, correctly returning a price for Pear, Apple, and Orange. However, in cell F8 VLOOKUP returns #N/A because there is no entry for "Lime" in column B. The #N/A result essentially means "not found", but it is returned as an error on the worksheet. We can catch this error and return an alternative result with the IFNA function.

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 function:

=IFNA(

Then we paste in the original formula like so:

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

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 effectively hidden. The final formula in cell I5 looks like this:

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

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. Inside IFNA, VLOOKUP returns #N/A as before. IFNA detects the #N/A error and returns an empty string ("") instead, which displays like an empty cell. If you would rather display a message like "Not found", simply modify the formula to include the message like this:

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

Note that the message must be enclosed in double quotes. The screen below shows how this modified formula behaves on the worksheet:

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:

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

Notice the structure is exactly the same. The original formula appears as the first argument inside IFERROR and the custom result ("") is the second argument. However, unlike IFNA, IFERROR will catch any error. This makes IFERROR a more blunt instrument since it will trap many kinds of errors. For example, if a function name is misspelled, Excel will normally return the #NAME? error:

=ZLOOKUP(H8,$B$5:$C$16,2,FALSE) // returns #NAME?

Above there is no function called "ZLOOKUP", so Excel returns #NAME?. IFERROR will catch this error as well, even though it has nothing to do with the operation of the formula:

=IFERROR(ZLOOKUP(H8,$B$5:$C$16,2,FALSE),"") // returns ""

In other words, IFERROR may unintentionally hide other errors and obscure an important problem. As a result, it makes more sense to use the IFNA function if the intent is to manage #N/A errors only.

Other error functions

Excel provides a number of error-related functions, each with a different behavior:

• The ISERR function returns TRUE for any error type except the #N/A error.
• The ISERROR function returns TRUE for any error.
• The ISNA function returns TRUE for #N/A errors only.
• The ERROR.TYPE function returns the numeric code for a given error.
• The IFERROR function traps errors and provides an alternative result.
• The IFNA function traps #N/A errors and provides an alternative result.

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.