What is IFNA?
The IFNA function returns a custom value if a formula returns the N/A error value. It happens mainly while using the VLOOKUP function in Excel.
Instead of seeing N/A errors, you can use the IFNA function to return a custom value like “Not Found” or “Not applicable”.
What is the IFNA formula in Excel?
In MS Excel, the IFNA function syntax includes 2 required arguments.
Syntax: =IFNA(value, value_if_na).
• value – the value we want to check.
• value_if_na – the custom value to return if the value to check is N/A.
Notes
– If value is a formula, the IFNA function will return the formula’s results if any.
– If value is empty, the system does not consider it as an error and 0 will be displayed.
– If the value_if_na argument is empty or if you use “” as the argument, the system will display 0 in the cell as it does not consider it as an error.
Examples
Return your custom value instead of a N/A error message
In this example, we want to replace any N/A errors by a custom value; value_if_na is “Not found”. Matthew is the only name with an error message as value so the value is replaced by “Not found” next to his name.
Return an empty cell instead of a N/A error message
If we wanted to replace a N/A error message by an empty result in the previous example, we would have changed the N/A formula in Excel with the following: =IFNA(value,””) or =IFNA(value,).
How to use VLOOKUP and IFNA together?
To use the VLOOKUP and IFNA functions together, replace the value argument by the VLOOKUP formula.
Example
In this example, if the VLOOKUP formula result is N/A, the value will be replaced by our custom value; value_if_na is “Not found”.
What if I want to catch all other errors?
If you want to replace other error messages by a custom value or an empty result, use the IFERROR function in Excel. The IFERROR function will replace all these errors: NULL!, N/A, REF!, VALUE!, DIV/0!, NUM! or NAME?.
To learn more about the IFERROR function in Excel or in Google Sheets, check our tutorial.
If you want to master the art of Excel Spreadsheets, join our Corporate Level MS Excel Course now!
