The Excel IFNA function returns a user-defined value if a formula results in an #N/A (value not found) error.
IFNA is a part of the logical functions and is compatible with Excel 2013 and above.
How to use the IFNA Function in Excel?
The IFNA function in Excel is designed to handle #N/A errors gracefully. It allows you to display a custom message or alternative result when an #N/A error occurs. This function is especially helpful in lookup formulas like VLOOKUP, HLOOKUP, or XLOOKUP, where missing data might cause #N/A errors, which can be confusing.
Syntax:
=IFNA (value, value_if_na)
The function uses two arguments:
- value: This is the formula or expression you want to evaluate. It could be a VLOOKUP function or any other formula that might result in an #N/A error.
- value_if_na: Excel will display this if the formula returns #N/A. It could be a custom message (e.g., “Not Found”) or a default value.
Trap and handle #N/A errors
In the example, we’ll use the classic VLOOKUP function. To simplify the table containing products and prices, use a named range for the B3:C8 range. Add the name ‘data’ for the range. Type the following formula in cell F5:
=IFNA(VLOOKUP(E5, data,2,0),”Not found”)
As the picture shows, VLOOKUP gets the prices for Product 3 and Product 4. What if the source range does not contain Product 11? Begin the expression with the IFNA function. The first argument is the VLOOKUP function. Add a “Not found” string as a second argument to manage errors. The next chapter will examine an error-handling solution that does not use the IFNA function.
Error handling without IFNA function
For Microsoft 365 users, the XLOOKUP function simplifies error handling without needing additional functions like IFNA. One key feature of XLOOKUP is its built-in error handling through the fourth argument, which allows you to define a custom message or fallback value if the lookup value is not found.
Formula:
=XLOOKUP(E3, Product, Price, “Not found”)
In XLOOKUP, the syntax includes an optional fourth argument, if_not_found, that specifies what to display if there’s no match.
Differences between IFERROR and IFNA functions
If you have other errors like # REF or # DIV/0, you can use the IFNA function to manage only the #N/A errors. We recommend you use the IFERROR function.
In the picture below, you can compare how these functions work. To calculate the average sales price, apply the formula in column F:
=D3/C3
=in cell E6, the formula returns with a #DIV/0! error. In cell G6, IFERROR can manage the output and returns with the user-defined “error” string. The result is the same if you are looking for the #VALUE! error in cell E7.
Other error-handling functions
If you work with formulas, you’ll face various errors, not just #N/A. Keep in mind that the IFNA function only handles the #N/A error.
Here is the list of other error-handling functions in Excel:
- ISNA function returns TRUE for #N/A errors only.
- ISERROR function returns TRUE for any error.
- ERROR.TYPE function returns a code to identify the error.
Additional resources: