## What Is IFNA Function In Excel?

The

IFNAfunction in Excel is an inbuiltLogicalfunction. It returns the specified value if the given formula’s output is the#N/Aerror. Else, theIFNAExcel function output is the formula’s return value.

Therefore, users can use the **IFNA()** to capture the **#N/A** errors and display a customized message in place of them.

For example, the following table contains a list of error values.

Suppose we must check the error values for the **#N/A** error and show the result as **Yes** if the specific cell contains the **#N/A** error and the cell value otherwise. And assume the column B cell range B2:B8 are the target cells. Then, applying the **IFNA **Excel function in the target cells can fetch us the required result.

The **IFNA() **accepts the source data cell as the first argument, and the second argument shows the message we must display instead of the **#N/A** error.

Cell A3 is the only cell in the range A2:A8 that contains the **#N/A** error. And hence, the target cell B3 formula returns **Yes** as the output.

##### Table of contents

###### Key Takeaways

- The
**IFNA**Excel function checks if a value is the**#N/A**error or if a formula returns the**#N/A**error. And if the condition holds, the**IFNA()**returns the value we specify. Otherwise, it returns the value or the formula’s output it verified for the**#N/A**error. - The
**IFNA**function helps trap the**#N/A**error when working with massive data sets and allows users to display a customized message instead of the error value. - As inputs, the
**IFNA**function accepts two mandatory arguments,**value**and**value_if_na**. - The
**IFNA**function works well with the**value**argument being expressions such as the**VLOOKUP()**and the**INDEX-MATCH**formula.

### IFNA() Excel Formula

The **IFNA **Excel function syntax is:

where,

**value**: The value or expression to check for the**#N/A**error.**value_if_na**: The value the**IFNA**Excel function should return if the given value or the expression result is the**#N/A**error.

Both the arguments in the **IFNA()** are mandatory.

### How To Use IFNA Excel Function?

The steps to use the **IFNA **Excel function are as follows:

- First, ensure the given value or the formula is accurate.
- Then, choose the required target cell and enter the
**IFNA()**. - Finally, press
**Enter**to view the**IFNA**function return value.

Here is an example to explain the above steps in detail.

The table below shows a list of numbers.

Suppose the requirement is to determine if the given numbers are multiples of 2 and display the output in column B. Then, here is how we can apply the **IFNA()** in the target cells and get the required data.

**To begin with, select the target cell B2, enter the below formula, and then, press Enter.***=IFNA(IF(MOD(A2,2)=0,”Number is a multiple of 2.”,#N/A),”Number is not a multiple of 2.”)*

We can also select the target cell and go to**Formulas**→**Logical**→**IFNA**to apply the function using the**Function Arguments**window.

Next, we must enter the values in the two fields in the**Function Arguments**window.

Finally, clicking**OK**will execute the**IFNA()**in the target cell.**Next, apply the above formula in cell range B3:B6 using the fill handle.**

Let us consider the cell B6 expression to check how the formula works. The**IF()**is the**value**argument. So, first, the**MOD()**finds**7 mod 2**to return**1**, which is not equal to**0**. Thus, the**IF()**condition is**FALSE**, and the function returns the**FALSE**value,**#N/A**.

Finally, the**IFNA()**checks the**IF()**return value for the**#N/A**error. And as the**IF()**output is the required error value, the**IFNA()**returns the message “**Number is not a multiple of 2**”, as the status.

### Examples

Check out the following examples to apply the **IFNA **Excel function in the best way possible.

#### Example #1

Here is an example of the **IFNA **with the **VLOOKUP excel function **as the **value** argument.

The first table shows a team’s appraisal details.

Suppose the second table contains employee names, and you need to update their appraisal status in cell range B15:B16 based on the first table. Then, here is how we can apply the **IFNA **Excel function in the target cells and get the status data.

**Step 1:**First, select the target cell B15, enter the below formula, and press**Enter**.

*=IFNA(VLOOKUP(A15,$A$2:$B$11,2,0),”Appraisal Pending”)*

**Step 2:**Next, drag the excel fill handle down, and enter the formula in cell B16.

The **VLOOKUP() **looks up the employee name specified in the second table in the first table. It returns the corresponding appraisal status for the given employee name from the first table. Else, it returns the **#N/A** error.

In the case of the cell B15 formula, the **VLOOKUP()** returns **Gretchen Hines’s **appraisal status, **Complete**, as the name is present in the first table. And thus, the **IFNA() **result is the **VLOOKUP()** return value, **Complete**.

On the other hand, the cell A16 value, **Kevin Reed**, is not present in the first table. So, the **VLOOKUP()** output is the **#N/A** error. And the **IFNA() **displays the message “**Appraisal Pending**” as a replacement for the error value.

#### Example #2

This illustration shows how the **IFNA **Excel function works when the **value **argument is a formula containing the **INDEX** and **MATCH** excel functions.

The first table shows an items list with their weight details.

And assume we need to determine the weight for the item specified in cell E2 of the second table based on the first table data and display the result in cell F2.

Then, we can apply the **IFNA **Excel function in the target cell to get the required data.

**Step 1:**To start with, select the target cell F2, enter the below formula, and then, press**Enter**.

*=IFNA(INDEX(B2:B11,MATCH(E2,A2:A11,0)),”Data Not Available”)*

First, the **MATCH() **checks for the cell E2 value, **item_15**, in the cell range A2:A11. As the required item is not present in the specified cell range, the function returns the **#N/A** error. And thus, the **INDEX excel function** also returns the **#N/A** error as the value the **#N/A** is not present in the cell range B2:B11 to return the cell reference or value at the row-column intersection.

Finally, as the given formula (**value** argument) output is the **#N/A** error, the **IFNA() **returns the message “**Data Not Available**” to display instead of the error value.

#### Example #3

This example explains using the **IFNA **Excel function as an array formula in excel.

The first table shows a list of students and their score details.

Consider the second table contains some students’ names, and we must get their scores in cell range F2:F5 based on the first table data. Then, we can execute the **IFNA **Excel function as an array formula in the target cells and achieve the required data.

**Step 1:**First, select the cell range F2:F5 and then, enter the below formula.

*=IFNA(VLOOKUP(E2:E5,A2:B11,2,0),”Data Not Found”)*

**Step 2:**Next, press the**Ctrl**+**Shift**+**Enter**keys to execute the above expression as an array formula.

*{=IFNA(VLOOKUP(E2:E5,A2:B11,2,0),”Data Not Found”)}*

The **VLOOKUP() **looks up the cell range E2:E5 values in the first table to return each student’s score based on the first table. As it finds the cells E2 and E4 values in the first table, it returns their respective scores, **87** and **77**. And as the function does not find the cell E3 and E5 names in the first table, it returns the **#N/A** error.

And, based on the **VLOOKUP()** outputs, the **IFNA()** returns the message “**Data Not Found**” if the **VLOOKUP() **output is the **#N/A** error. Otherwise, its output is the looked-up score the** VLOOKUP() **returns.

### Important Things To Note

- The
**IFNA**function works only for the**#N/A**error in a spreadsheet. - For the
**value**or**value_if_na**argument supplied as an empty cell, the**IFNA**Excel function considers it as an empty string (“”). - Suppose the
**value**argument is an array formula. Then the**IFNA**function output is an array of return values for the cell range specified in the**value**argument.

### Frequently Asked Questions (FAQs)

**1. Where is the IFNA function in Excel?**

The **IFNA** function in Excel is in the **Formulas** tab. Navigate the path **Formulas** → **Logical** → **IFNA **to access and apply the function in the required target cell.

**2. How to apply IFNA function in Excel VBA?**

We can apply **IFNA **function in Excel VBA using the method mentioned below:**Application.Worksheetfunction.IfNa(value,value_if_na)**

In the above method, the arguments, **value**, and **value_if_na** have the same meaning defined in the **IFNA() Excel Formula** section.

The example below shows how to apply the abovementioned method in Excel VBA.

The following table contains a list of values.

Suppose the requirement is to determine if the given value is the **#N/A** error, display the status in column B, and the given data otherwise. Then, here is how we can apply the **IFNA** function in Excel VBA using the method mentioned above.

• **Step 1: **With the current sheet containing the above table open, press the **Alt** + **F11** keys to open the VBA Editor.

• **Step 2: **Choose the required **VBAProject**, click **Insert**, and then **Module** to open a new module window, **Module1**.

• **Step 3: **Enter the code to apply the **IFNA** function in the target cells.**Sub IFNA_fn()**

Dim Ws As Worksheet

Set Ws = worksheets(“FAQ_2”)

Ws.Range(“B2”) = Application.WorksheetFunction.IfNa(Ws.Range(“A2”), “The value is #N/A error.”)

Ws.Range(“B3”) = Application.WorksheetFunction.IfNa(Ws.Range(“A3”), “The value is #N/A error.”)

Ws.Range(“B4”) = Application.WorksheetFunction.IfNa(Ws.Range(“A4”), “The value is #N/A error.”)**End Sub**

• **Step 4: **Using the **Run Sub/UserForm **option, execute the code.

• **Step 5: **Go to the active worksheet to view the required result.

The method checks the value in the specified cell reference. And if the data is the **#N/A** error, as in cell A3, the function returns the value, “**The value is #N/A error**”, in the corresponding target cell B3. Otherwise, the **IFNA()** output is the given value, as in the target cells B2 and B4.

**3. What is the difference between the IFNA and IFERROR functions?**

The difference between the **IFNA** and **IFERROR** functions is that the **IFNA()** traps only the **#N/A** error. On the other hand, the **IFERROR()** identifies the different Excel errors, such as the **#N/A**, **#DIV/0**, **#NAME**,** #NUM**, **#VALUE**, and **#REF**.

The **IFNA()** best suits the **LOOKUP** functions, as these functions ideally return the **#N/A** error if they do not find the lookup value. And the **IFERROR() **is useful when the calculations involve complex formulas containing Excel functions returning different error values.

### Download Template

This article must be helpful to understand the **IFNA Excel Function**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to IFNA Excel Function. Here we learn how to use IFNA() formula along with step by step examples and downloadable excel template. You can learn more from the following articles –

## Leave a Reply