The Excel IFNA function returned a custom result when #N/A error occurred; otherwise returns the formula result.
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.
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
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
The result is as shown below. #N/A error disappears in cell H5, as well the #NA error in cell I5 and I8.
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 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.
Use the IFNA function if available in your excel version. The IFNA function is better than the combination of IF functions and ISNA functions.