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 compatible with Excel 2013 and above.
How to use IFNA Function in Excel?
As stated above, the main goal of using the IFNA function is to replace the built-in #N/A error with a descriptive, user-friendly output.
Using the IFNA function, you can manage #N/A errors and create easy-to-understand output. If you are working with lookup functions, especially VLOOKUP and HLOOKUP.
Syntax and arguments
=IFNA (value, value_if_na)
The function uses two arguments:
- value – The result of the formula if the result is a value
- value_if_na – The user-defined value if the function returns with an #N/A error
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 you see in the picture below, 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.
In the next chapter, we will take a closer look at an error handling solution without using the IFNA function.
Error handling without IFNA function
Are you Microsoft 365 user? We have a piece of great news:
Tip: if you are using XLOOKUP, no additional error-handling function is required. The 4th argument of the function helps you to produce an error-free output in case of any errors.
Differences between IFERROR and IFNA functions
You can use the IFNA function to manage only the #N/A errors if you have other errors like #REF, #DIV/0! 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 functions
If you are working with formulas, you’ll face various types of 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.
