Excel IFNA Function

HomeExcel FunctionsExcel IFNA Function (Example + Sample File)

What is EXCEL IFNA FUNCTION

The Excel IFNA Function is listed under Microsoft Excel's Logical Functions category. It returns a specific value if an #N/A error occurs. Unlike IFERROR, it only evaluates the #N/A error and returns the value you specified.

How to use it

To learn how to use the IFNA function in Excel, you need to understand its syntax and arguments:

Syntax

IFNA(value, value_if_na)

Arguments

  • value: The value you want to test for #N/A error.
  • value_if_na: The value you want to return if an error occurred.

Notes

  • If you skip specifying any argument, IFNA will treat it as an empty string (“”).
  • If a value is an array then it will return the result as an array.
  • It will ignore all other errors #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.

Example

To master the IFNA function we need to try it out in an example and below is one which you can try out:

In VLOOKUP function, #N/A occurs when the lookup value is not in the lookup range and for that we have specified a meaningful message using IFNA.

Note: IFNA is introduced in Excel 2013 so it is not available in the previous versions.

Related functions

This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.