Excel IFNA function

How to use the Excel IFNA function

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:

= IFNA(VLOOKUP(value, data, column, 0), “Lookup value not found!”)

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

How to use the Excel IFERROR function

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.

How to use the Excel IF function

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email