IFNA Function

The IFNA function is a nice way to capture and manage #N/A errors without capturing other errors. The IFNA function can be used to capture errors resulting from performing lookups with MATCH, VLOOKUP, HLOOKUP, XLOOKUP, etc functions. The IFNA function lets users return a custom result or perform a different task the formula generates a #N/A error and a regular result when no error is generated. IFNA function will handle the #N/A error only and other errors, if any, will be shown as usual.

Example:

As shown above, the IFNA function captures the #N/A error that occurs with the VLOOKUP function. For now, just pay attention to the IFNA function and ignore the VLOOKUP function. We will discuss the VLOOKUP function in a separate tutorial.

=IFNA(VLOOKUP(F15,$B$5:$C$17,2,0),"Not Found")

When the value from column F is found in the range $B$5:$C$17, specifically in column B, it gives the rate from column C corresponding to the matched value in column B. If the lookup value, in this case the value from column F, is not found then VLOOKUP returns an #N/A error and the IFNA function captures this error and returns a more user-friendly result. In this case, #N/A is replaced with “Not Found”.

IFERROR or IFNA?

The IFERROR function is definitely a useful function and it catches all kinds of errors. While this is useful, it may sometimes lead to inaccurate/unexpected results. If, for example, there is a typo in a formula, Excel will return #NAME? error, but IFERROR will suppress the error and return the alternative result. This can hide an important problem. In many cases, it is more meaningful to use the IFNA function, which only captures the #N/A error.

Error-Related Functions of Excel

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

• The ISERR Function will return True for all types of errors except #N/A error.
• The ISERROR Function will return True for all types of errors.
• The ISNA Function will return True only for #N/A error.
• The ERROR.TYPE Function will return the numeric code for an error.
• The IFERROR Function captures an error and gives the option to place an alternative result.
• The IFNA Function captures #N/A errors and gives the option to place an alternative result.