Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

IFNA Function

Returns a specified alternate value if a formula results in an #N/A error

What is the IFNA Function in Excel?

The IFNA function in Excel returns an alternate value as specified by the user if a formula results in an #N/A error. It is a Logical function that was introduced in MS Excel 2013. The purpose of the IFNA function is to use an alternate value instead of the #N/A error.

 

Formula

=IFNA(value, value_if_na)

 

The IFNA function uses the following arguments:

  1. Value (required argument) – It is the formula or expression to be checked for an #N/A error.
  2. Value_if_na (required argument) – It provides the specified value if the result of the formula or expression is N/A error.

 

How to use the IFNA Function in Excel?

It is a built-in function which can be used as a worksheet function in Excel. Let’s take an example:

Using the value in A3 and #N/A in A4, let’s see how to apply IFNA to get rid of #N/A.

 

IFNA Function

 

When we use IFNA for both rows, the result would be:

 

IFNA Function - Example

 

Uses of the IFNA Function in Excel

To understand the uses of the IFNA function, let’s consider a few examples:

 

Example 1

If we need to find out the prices of two commodities using the data below:

 

IFNA Function - Example 1

 

Now we need to find out the price of Pepper and Banana from this information. If we just use the VLOOKUP formula, the result will be #N/A for Banana.

 

IFNA Function - Example 1a

 

However, if we use IFNA, we can instruct Excel to give us 0 instead of #N/A, as shown below:

 

IFNA Function - Example 1b

 

The formula used here is:

 

IFNA Function - Example 1c

 

Here, IFNA function tested the results of VLOOKUP function. VLOOKUP returned the #N/A error value as it couldn’t find Banana in the given lookup range. However, IFNA returned the value 0 in the cell instead of the standard #N/A error value.

On a similar basis, we can use the HLOOKUP formula combined with IFNA.

 

Example 2

As the IFNA function is more specific and targets only specific error, which is #N/A, it is more appropriate to use than IFERROR function in certain circumstances. All other errors, such as #DIV/0! or #REF!, are ignored by the IFNA function. IFERROR, on the other hand, would trap other errors too.

Consider the data below:

 

IFNA Function - Example 2

 

The user made a typo error by putting 0 for the price per unit of capsicum. Now, when he wishes to derive the quantity for each vegetable, he would get a #DIV/0! error for the quantity of capsicum. Let’s see how the results would be different under IFNA function and IFERROR function.

When we use IFERROR formula as shown below, the result would be 0, as IFERROR considers all errors.

 

IFNA Function - Example 2a

 

IFNA Function - Example 2b

 

Since the quantity is calculated by dividing the total price by price per unit, MS Excel would display the #DIV/0! error for vegetables whose prices were (erroneously) typed as 0 per unit.

This is a kind of error that we will handle in a different manner, compared to the way we will handle the #N/A error.

However, IFERROR considered it as an error and returned 0 for it.

On the other hand, IFNA would return #DIV/0! error as it considers only the #N/A error.

 

IFNA Function - Example 2c

 

IFNA Function - Example 2d

 

So, IFNA becomes handy when you are only looking for the #N/A error only. The function becomes useful when we are handling extensive and complicated data sets where the user would miss different type of errors on the sheet.

 

More on the IFNA Function in Excel

  1. The IFNA function helps to trap error functions and is very useful when handling large amounts of data.
  2. Remember that if the user provides value_if_na as an empty cell, the function would treat it as an empty string value (“”).
  3. It is quicker when comes to troubleshooting, as it is more specific compared to IFERROR, ISERROR, ISERR, etc.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!