How to use IFNA Formula in Excel?

The IFNA formula in Excel returns user defined value if #N/A error occurs. The IFNA formula in Excel is a part of logical formulas.

The article in this webpage describes how to use IFNA formula in Microsoft Excel with syntax, detailed explanation and examples.

Subscribe our YouTube channel to know the latest updates, tips and tricks.

What is IFNA Formula in Excel?

The IFNA formula in Excel returns the specified value if a formula returns the #N/A error, otherwise returns the result of the formula. The IFNA formula in Excel is used to trap and handle the #N/A errors in a formula.

The IFNA formula was introduced in Excel 2013 and is available in all subsequent versions. The use of the IFNA formula is to get a value you specify instead of the #N/A error. So, the Excel IFNA formula is an easy way to handle only #N/A error without handling other errors. The main difference between IFNA and IFERROR formula is, the IFNA formula can handle only #N/A error, but the IFERROR formula can handle all errors such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!.

When a formula returns an #N/A error, it means the value is not available or not found in the formula. The #N/A error looks like an error in the cell and the user may not be interested to show or print the same in the output. In such situation, the IFNA in Excel will be useful as it replaces the #N/A error with user specified value without handling more serious errors.

What is the Syntax of IFNA Formula in Excel?

The syntax of IFNA formula is as follows:

=IFNA(value, value_if_na)

What are the Arguments of IFNA Formula in Excel?

The arguments for IFNA formula are explained below in detail and summary for your easy reference:

Value (Required Argument)

This is the formula or logical expression that is checked for #N/A error.

Value if NA (Required Argument)

This is the user specified value to return if the formula returns #N/A error.

IFNA Arguments:

• Value (Required) – formula or expression.
• Value if NA (Required) – given value in case of #N/A error.

How to use Excel IFNA Formula with Examples?

Example 1 (simple IFNA formula)

In the below example, there is #N/A error due to VLOOKUP formula in cell F6.

=VLOOKUP(“Cherry”,B5:D9,2,0)

To handle the #N/A error in the formula, you can use the IFNA formula. In the below IFNA formula example, the “Not Available” is specified in case of #N/A error. The IFNA formula along with VLOOKUP formula in cell F6 is:

=IFNA(VLOOKUP(“Cherry”,B5:D9,2,0),”Not Available”)

Example 2 (value)

In IFNA formula, you can define the specified text, another formula or any value to handle the #N/A error. In the below IFNA example, the specified value is zero (0) which does not need any double quote.

=IFNA(VLOOKUP(“Cherry”,B5:D9,2,0),0)

In the IFNA formula, if you ignore the specified value and just add comma (,) then you will get zero (0) in case of #N/A error.

=IFNA(VLOOKUP(“Cherry”,B5:D9,2,0),)

Example 3 (nested IFNA formula)

You can use the nested IFNA formula in multiple formulas to meet the conditions. In the below example of IFNA formula, if there is no #N/A error in first VLOOKUP formula then it will go to the next VLOOKUP formula and if still the #N/A error occurs then you will get the specified text as “Not Available”.

=IFNA(VLOOKUP(“Cherry”,B5:D9,2,0),
IFNA(VLOOKUP(“Apple”,B5:D9,2,0),”Not Available”))

Example 4 (empty)

In case you need the blank or empty cell as output then you need to keep the quotes without space as shown in the below Excel IFNA example:

=IFNA(VLOOKUP(“Cherry”,B5:D9,2,0),“”)

What are the Key Points for IFNA Formula in Excel?

You must remeber the below key points while using the Excel IFNA formula:

• The first step should be to check if any error in formula, before adding the IFNA formula to it.
• If any of the argument is blank then the IFNA formula considers it as empty string value and not an error, which is equal to double quote without space (“”) in formula and results as blank in cell.
• The IFNA in Excel can handle only the #N/A error.
• The Excel IFNA formula can replace the #N/A error with user defined text, number, another formula, or empty string.
• The IFNA formula was introduced in Excel 2007, and it is available in all subsequent versions.
• To manage errors in Excel 2003 and old versions, you can use the ISERROR with IF formula.
• The alternative of IFNA formula with error handing are:
  • IF + ISNA to handle only #N/A errors.
  • ISERR to handle all errors except for #N/A errors.
  • IFERROR to handle all errors.