How to Use Excel IFNA Function (5 Suitable Examples)

Excel IFNA function is primarily used to handle the #N/A errors. It returns a specific value as per your instruction if such a #N/A error occurs; otherwise, it returns the absolute value of the function. In this article, we’ve discussed Excel IFNA function in detail with 6 suitable examples.

We will be using the following product price list as our demo dataset to explain all the examples regarding the IFNA function. Now let’s have an overview of our article:

For conducting the session, I will use Microsoft 365 version.

Introduction to Excel IFNA Function

• Function Objective

The IFNA function is used to tackle the #N/A error.

• Syntax

IFNA(value, value_if_na)

• Arguments Explanation

• Return Parameter

Value of the first argument or an alternative text.

How to Use Excel IFNA Function: 5 Suitable Examples

In this section, we will show you some easy and simple examples to clarify the concept of this IFNA function. So, you can implement the IFNA function to make your work easier. Let’s see the examples below.

1. Basic Usage of the IFNA Function in Excel

In this example, we will show you the very basic usage of the IFNA function.

In the above image, there’s already #N/A within cell E14. So if we refer to cell E14 within the value field of the IFNA function, then the value specified in the value_if_na field will appear in cell E15.

• Now insert the formula within cell E15,

As we press the ENTER button, we can see the Missing message appear within cell E15 as predicted.

2. Usage of IFNA Function with VLOOKUP Function to Return a Meaningful Massage

First of all, we want to describe the usability of the IFNA function with the VLOOKUP function. This is the most common usage of the IFNA function.

You may want to use the VLOOKUP function to extract values based on a lookup value. Now what’s the inconvenient thing about this VLOOKUP function? It has a complex syntax, as well as, it requires a bundle of rules to follow to work properly.

So by any means, if you do any of the mistakes, then the VLOOKUP will show the #N/A error. Which is nothing but an error that represents, “value is not available”.

Now, suppose you don’t want to allow the #N/A message throughout your dataset. But we’re interested in showing a more meaningful message.

In that case, you can use the IFNA function along with the VLOOKUP function to tackle the error message in a better way.

2.1 Using IFNA Function with VLOOKUP Function in Single Sheet

Let’s say for any #N/A error message, we want to show “Missing”. In the image below, we can see the #N/A message within cell E15.

• The formula within cell E15 is:

If we look closely at the data table below, we can see that the lookup value is Cereal. But there is no such value in the first column of the data table. As a result, #N/A error is showing there.

Now if we want to show Missing in replace of #N/A, then we have to use the following formula with the IFNA function.

This is how we can use the IFNA function along with the VLOOKUP function.

2.2 Apply IFNA and VLOOKUP Functions to Search Multiple Findings

Here, we will search for the prices of multiple products at once.

• So, first, write the product’s names. Here, we have written it in D15:D17 cells.
• Then use the following formula in the E15 cell.

• Press ENTER and get all of their prices.

2.3 Use IFNA Function with VLOOKUP Function Within Multiple Sheets

Suppose you need to use the VLOOKUP function within multiple sheets. In this case, if there is no similar lookup value in the dataset then you will get an error as output. So, we will combine the IFNA function with the VLOOKUP function to search items across multiple sheets.

First, we will make a list of all products and then apply Data Validation to find the price of a product from that list.

• Make the list for all products.
• Click E7 cell >> from Data tab >> under Data Tools group >> from Data Validation >> select Data Validation.

As a result, you will see the dialog box named “Data Validation”.

• In that dialog box >> select List in the Allow box >> set the reference of all products in the Source box >> press OK.

So, you will see a drop-down list beside the E7 cell and can select any product from this drop-down list.

• Now enter the following formula in the E8 cell.

• Then you will get the unit price of the selected product in the E7 cell. As I have selected Juice in the E7 cell which is not listed in any of the sheets, I get “Missing” as output.

3. Catch #NA Error Using IFNA Function with MATCH Function

While you are using the MATCH function, you can also get a meaningful answer instead of getting the #NA error.

Here, we want to find the row index of a defined product. If the product name doesn’t exist then we want to show “No matched product” in the place of the row index.

• Use the corresponding formula in the E15 cell and press ENTER to get the result.

4. Apply IFNA with INDEX-MATCH Functions to Suppress #NA Error

Here is another example of the application of the IFNA function. You can use the IFNA function with the combination of INDEX, and MATCH functions to make the #NA error understandable.

• In cell D15, enter the following formula. See the result.

5. Use of IFNA Function to Solve a Mathematical Problem in Excel

Suppose you want to solve an easy mathematical problem like finding the highest value or calculating a summation. But mistakenly, there is a #N/A error in your dataset. Then you will find another #N/A error after doing that mathematical problem.

See the below image, you will get my point. It is so irritating that a single #N/A error becomes the barrier to doing a tiny calculation.

• To solve the problem, create a new column E >> paste the following formula in the E5 cell.

• Use the Fill Handle icon to copy the same formula for other cells of the E column. As a result, you will get a 0 instead of the #N/A error.

• Now do your needed calculation with the data of the E column. Like below, I have used the MAX function and got the highest selling price among them.

IFERROR Vs IFNA Function in Excel

The IFERROR function handles a wide range of errors whereas the IFNA function tackles only the #N/A i.e. not available error.

For instance, if there’s any typo in your formulas then Excel may return the #NAME error. In this case, the IFERROR function can handle the error by showing an alternating text in replace of the #NAME message.

On the other hand, the IFNA function cares only about the #N/A error. This can display an alternative text in replacing the #N/A error showing.

So, if you want to handle only the #N/A error, then it’s the best practice to use the IFNA function in lieu of the IFERROR function. For the other types of errors, you can use the IFERROR function.

See the above image, there is a division error (#DIV/0!) in our dataset. The IFNA function can’t respond to this error, whereas the IFERROR function can!

Things to Remember

📌 If a cell is empty, then it’s treated as an empty string (“”) but not as an error.

📌 If you don’t fill up the value_if_na field, then the IFNA function will consider this field as an empty string value (“”).

Frequently Asked Questions

1. Which versions of Excel have the IFNA function?

From the version of Excel 2013 to the earlier versions, you will get this IFNA function.

2. Is false and 0 the same in Excel?

Yes, we can use 0 instead of FALSE in any function of Excel.

3. How do you check if a cell is null or empty in Excel?

You can use the ISBLANK function to find the empty cells. This function will return TRUE to the empty cells.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You are recommended to download the Excel file and practice along with it.

Conclusion

To sum up, we have discussed every possible aspect with corresponding examples regarding the Excel IFNA function. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.

<< Go Back to Excel Functions | Learn Excel