What is the Excel IFNA Function?
The Excel IFNA function returned a custom result when #N/A error occurred; otherwise returns the formula result.
The IFNA function is an improvement provided by Excel to trap and handle #N/A error in a formula without having to write repetitive formulas and IF statement.
IFNA function trap and handle #N/A error only, to trap and handle more errors use excel IFERROR function
IFNA Syntax
IFNA(value, value_if_na)
value, required, the argument to check for N/A error, could be a value, a reference or a formula
value_if_na, required, the custom return value if #N/A error occurred.
How to Use IFNA Function in Excel
IFNA VLOOKUP
The most often discussed error #N/A is when using the VLOOKUP function.
For example, there is data as shown below. There is an item name, and the price is in columns A, B, and C.
The VLOOKUP function is used to find prices in column H. The #N/A error appears in cell H5, the error also causes the formula in cell I5 and cell I8 to produce the #N/A error.
How to remove #N/A error in cell H5, so the formula in cell I5 and I8 does not produce a #N/A error?.
The Formula to find the price of each item in column H is as follows
=VLOOKUP(F3,$A$3:$D$15,2,FALSE)
To remove the #N/A error, you should modify the formula above by adding the IFNA function. The value argument is filled with the original VLOOKUP formula, and the value_if_na argument is filled with the number 0 (zero).
The Modified Formula
=IFNA(VLOOKUP(F3,$A$3:$D$15,2,FALSE),0)
The result is as shown below. #N/A error disappears in cell H5, as well the #NA error in cell I5 and I8.
Other Alternatives
Before Excel provides the IFNA function in Excel 2013, the only way to trap and handle the #N/A error is to use the IF and ISNA functions.
The Formula
=IF(ISNA(VLOOKUP(F3,$A$3:$D$15,2,FALSE)),0,VLOOKUP(F3,$A$3:$D$15,2,FALSE))
The result is as shown below.
There is no difference in results when using the IFNA function or using IF and ISNA functions. But pay attention to the formula. There are enough differences between the two.
- The IFNA function produces a simpler formula.
- The combination of IF functions and the ISNA function forces the same VLOOKUP function to be used twice, first in the logical_test argument and the second in the value_if_false argument.
The Verdict
Use the IFNA function if available in your excel version. The IFNA function is better than the combination of IF functions and ISNA functions.
