IFNA Function Examples – Excel, VBA, & Google Sheets
In this Article
This tutorial demonstrates how to use the Excel IFNA Function in Excel to so something if a calculation results in an #N/A error.
IFNA Function Overview
The IFNA Function tests if a calculation results in an #N/A Error. If not, it displays the calculation result. If so, it performs another calculation.
To use the IFNA Excel Worksheet Function, select a cell and type:
(Notice how the formula inputs appear)
IFNA Function Syntax and Inputs:
value – A value, calculation, or cell reference.
num_digits – What to do if value results in an #N/A error.
How to use the IFNA Function
The IFNA Function tests if a calculation results in a #N/A error, outputting a different value (or calculation) if an error is detected.
Alternatively, the IFERROR Function tests if any error occurs, including #N/A Errors.
Why Use the IFNA Function?
You might be wondering, why should I just use the IFERROR Function? Well when working with lookup functions (like the VLOOKUP), if the lookup value isn’t found the function will return an #N/A.
You might only want to handle these valid #N/A errors by setting error values to zero or blank.
But regular errors will still be shown, allowing you to identify if there is a calculation error with the formula.
IFNA in Google Sheets
The IFNA Function works exactly the same in Google Sheets as in Excel:
IFNA Examples in VBA
You can also use the IFNA function in VBA. Type:
For the function arguments (number, etc.), you can either enter them directly into the function, or define variables to use instead.