Explaining the IFNA function

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2016 for Mac, Excel for Mac 2011.

This post is focused on explaining the IFNA function and how you can use it for your specific needs. The IFNA function returns the value you specify if a formula returns the #N/A error value; otherwise it captures the formula’s result.

Syntax

IFNA(value, value_if_na)

The IFNA function syntax contains the following arguments.

ArgumentDescription
value
Required
The argument that is scanned for the #N/A error value.
value_if_na
Required
The value to return if the formula evaluates to the #N/A error value.

Remarks

  • If value or value_if_na is an empty cell, IFNA assumes it to be an empty string value (“”).
  • If value is an array formula, IFNA yields an array of results for each cell in the range defined in value.

Example

In the following example, IFNA tests the result of the VLOOKUP function. Because Seattle is not found in the lookup range, VLOOKUP brings back the #N/A error value. IFNA returns the string “Not found” in the cell rather than the standard #N/A error value.

Image explaining the IFNA function with VLOOKUP to prevent #N/A errors from being displayed.

Need more help?

Here are some resources you can use to help you with explaining the IFNA function and/or suggest a new tool or development to Excel.

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

How to avoid broken formulas

Leave a Reply

%d bloggers like this: