IFNA Function - Excel
Overview
The IFNA function in Excel is a useful tool for handling #N/A errors in a formula. The most common cause for this error is when using a lookup function and the value does not exist. This function allows you to provide an alternative result, whether a number, a statement or another function, whenever a #N/A error occurs in any of your formulas. Other functions can be nested into the IFNA function.
Example:
=IFNA(VLOOKUP(C2,$F$1:$G$8,2,FALSE),"No city found")
Syntax:
=IFNA(value, value_if_na)
value: The formula or expression that you want to evaluate.
value_if_na: The value to be returned if the value results in the #N/A error.
Examples
In the example above, instead of displaying the #N/A error message, like we have in the #N/A Example, we use the IFNA function to handle the error in a more effective way by having it return a statement that says “No city found”.
Instead of a statement we can also provide a number, a cell or another function as an alternative result. Here under the Number Return Example you can see that we have the same error as before but this time instead of a statement we have it return the number 999.
In the Cell Return Example we reference a different cell when the error occurs. We tell the IFNA function that we want to return cell C5 when we have an error and in this example you can see that we return the word “Nowhere” because that is what is in cell C5.
#N/A Example
Number Return Example
Cell Return Example
Nested Example
In this example, I'll demonstrate how you can nest a function into the IFNA function. When the error occurs we then have the IF function trigger to see whether the name we are searching for is “Steve” or not. If the name is “Steve” then the function will return “Somewhere in Utah” but if it is not then the function will return “Out of State”. In this case, the name was “John” so the result was “Out of State”.
