The Excel IFNA function returns a custom result when a formula generates the #N/A error, and a standard result when no error is detected. IFNA is an elegant way to trap and handle #N/A errors specifically without catching other errors.
Trap and handle #N/A errors
The value supplied for #N/A errors
=IFNA (value, value_if_na)
value - The value, reference, or formula to check for an error.
value_if_na - The value to return if #N/A error is found.
Use the IFNA function to trap and handle #N/A errors that may arise in formulas, especially those that perform lookups using MATCH, VLOOKUP, HLOOKUP, etc. The IFNA function will only handle #N/A errors, which means other errors that may be generated by a formula will still display.
You can also use the IFERROR function to catch the #N/A errors, but IFERROR will also catch other errors as well.
IFNA with VLOOKUP
A typical example of IFNA used to trap #N/A errors with VLOOKUP will look like this:
When VLOOKUP can't find a value in a lookup table, it returns the #N/A error. The IFERROR function allows you to catch errors and return your own custom value when there is an error. If VLOOKUP returns a value normally...
The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here. Using the lookup table...
The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or...
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
The Excel ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. You can use the ISNA function with the IF function test for an error and display a friendly message when it appears...