Description
The Excel IFNA function is a logical function to trap and handle the #N/A error type in a formula. It returns a custom result if a formula generates a #N/A error, and returns the result of the formula if no #N/A error is detected. IFNA is a more specific variant of the IFERROR function and a great alternative to trap and handle #N/A errors without catching other errors.
Syntax
IFNA( value, value_if_na )
Arguments
value – The argument that is checked for an #N/A error
value_if_na – The value to return if an #N/A error is detected in the value argument
Return value
The value that has been specified for error conditions
How to use the IFNA function in Excel
The IFNA function detects #N/A errors in a formula and returns a custom result or formula if an #N/A error is detected. Unlike the IFERROR function, it only catches #N/A errors and ignores any other type of error.
#N/A errors specifically occur in lookup formulas like VLOOKUP, XLOOKUP, MATCH, etc. To catch an #N/A error in a VLOOKUP statement, use the following syntax:
For all other functions that might produce a #N/A error, the syntax is the exact same.
Additional Notes
Be careful, if no value is provided for the value argument, it will be evaluated as an empty string (“”) and not an error.
As the IFNA function only catches #N/A errors, all other error types are still displayed. If you want to catch any possible error instead, use the IFERROR function.
AND formula examples
Related Tutorials
Related Functions
Excel IFERROR function
The Excel IFERROR function is a logical function to trap and handle errors in a formula. It returns a custom result if a formula generates an error.
Excel IF function
The Excel IF function allows you to perform a simple logical test and returns one value for a TRUE result, and another value for a FALSE result.
