# How to Use IFNA Function in Excel – 5 Examples

Excel formulas generate the** #N/A** error when they are unable to locate or identify something. Use the **IFNA** function in Excel to detect such errors and replace them with **alternative messages** for users.

To say it another way, **#N/A** in Excel is a method of telling you that the value you’re looking for isn’t in the dataset it’s referencing. Your method of catching and handling that mistake is the **IFNA** function.

In this article, we are going to discuss some useful applications of the** IFNA** function in Excel with some examples along with other functions like **VLOOKUP, HLOOKUP, MATCH**, etc.

You may use the **IFERROR **function as an alternative to the **IFNA** function. In the following section, we will compare the **IFNA** function to the** IFERROR** function.

**Purpose of IFNA Function**

With Excel’s built-in **IFNA** function, you may determine whether a value corresponds to a given value and, when it doesn’t, have a customized or user-friendly value returned.

**Syntax of IFNA Function**

`=IFNA(argument, "return_if_N/A")`

**Arguments of IFNA Function**

**argument:** The value, formula, or reference to be checked in case of a #N/A error (required).

**return_if_N/A:** The value or customized text message to be returned if a #N/A error is found (required).

**5 Examples to Use of IFNA Function in Excel**

**Example #1: Use IFNA Function with VLOOKUP Function**

When the **VLOOKUP** function is not able to discover a match, it may produce **#N/A** errors. To catch these errors, use the **IFNA** function to check the result and define the value you want to be shown in place of the error. The **IFNA** function is very often used with the **VLOOKUP** function using the following syntax:

`=IFNA(VLOOKUP(), "customized text")`

Before applying the formula, you should understand the syntax of the **VLOOKUP** function.

**Syntax of the VLOOKUP function**

`=VLOOKUP(value, range, column_number, match_type)`

**The argument of the VLOOKUP function**

*value: **the value you want to lookup*

*range**: the range in the Excel sheet containing the lookup value*

*column_number**: The column number from which you want to return a value corresponding to the range.*

*match_type**: **TRUE** for the approximate match type, **FALSE** for the exact match type*

Let’s say you wish to find the ** sold quantity** of a specific seller named

**George**in our sample table given below. You are applying the following standard

**VLOOKUP**formula for this:

`=VLOOKUP(F2,B2:B9,2,FALSE)`

**Explanation:**

**George** isn’t present in the seller list, which is why **VLOOKUP** is unable to locate a match. So the **VLOOKUP** function returns an** #N/A** Error.

To show your customized text instead of the **#N/A** Error, apply the **IFNA** function as follows.

`=IFNA(VLOOKUP(F2,B2:B9,2,FALSE),"Not found in the seller list")`

Similarly, **IFNA** functions can be nested with **HLOOKUP, LOOKUP, XLOOKUP**, or any other function.

**Example #2: Combined application of IFNA Function with ISNA Function**

To check if the formula returns **#N/A** errors, the** ISNA** function is used. If a **#N/A** error is found, the return value of the **ISNA** function is **TRUE**, otherwise **FALSE**.

**Syntax of the ISNA Function: **

`=ISNA(value)`

The return value will be **FALSE** if we apply the** ISNA** function as shown below.

`=IFNA(ISNA(D2), "Custom text")`

**Explanation:**

- If cell
**F2**doesn’t contain the**#N/A**Error, then the return will be**FALSE**. - As
**#N/A**Error replaced as ‘’**TRUE**’’ or ‘’**FALSE**” returned by**ISNA**function. So the combined formula of the**IFNA**function and**ISNA**function will never return the**‘’Custom text’**’.

**Example #3: Application of IFNA Function with INDEX, MATCH Function**

The **IFNA** can detect **#N/A** errors produced by various lookup functions. Let’s utilize the **IFNA** function with the** INDEX MATCH** formula as below:

`=IFNA(INDEX(C2:C9, MATCH(F2, B2:B9, 0)), "Not found")`

**Explanation:**

**MATCH(F2, A2:A9, 0)**matches the value input in cell**F2**with the range**A2:A9**.- As
**George**is not present in the data range**A2:A9,**the**INDEX**and**MATCH**functions produce**#N/A Error**. - That’s why the
**IFNA**function returns**‘’Not found’’**.

**Example #4: Nested IFNA Function with SUMPRODUCT**

In this example, we want to avoid a **#N/A **contained row from **SUMPRODUCT**. For this, just insert the following formula in the output cell:

`=SUMPRODUCT((IFNA(B2:B9, 0)<>0)*D2:D9)`

**Explanation:**

**IFNA(A2:A9, 0)**replaces the**#N/A**contained cell with**0**.- Then, if this portion
**<>0**is included in the formula, it will return**TRUE**only if the return value of**IFNA(A2:A9, 0)**is not**zero**. - Finally, the
**SUMPRODUCT**function multiplies the range**D2:D**9 by the numerical return (**TRUE = 1**and**FALSE = 0**) of the Boolean logic**IFNA(A2:A9, 0)<>0**and returns the sum of the products.. (For row**7**the product comes to**0.)**

**Example #5: IFNA Function nested with multiple VLOOKUP function**

The **IFNA** function can also be used to perform multiple lookups on different sheets. If one VLOOKUP from SheetA fails, then a second VLOOKUP can be run in SheetB by using the formula.

`=IFNA(VLOOKUP(G3,B2:D6, 3, FALSE), VLOOKUP(G3, SheetB!A2:C4, 3, FALSE))`

**Things to Keep in Mind about IFNA Function**

- When working with big volumes of data, the
**IFNA**function comes in quite handy to catch**Errors**returned from functions. - Remember that the function will interpret the second argument,
**“return if N/A,**” as an empty string value (“”) if the user passes it as an empty cell. - Considering troubleshooting speed, it is quicker than the
**IFERROR, ISERROR,**etc, because it is more specific. **IFNA**Function is not available in the former versions of MS Excel 2013.

**IFNA Function VS IFERROR Function**

An Excel formula may produce a variety of errors, including #**VALUE, #N/A, #NAME, #REF,** #DIV/0, #NUM, and others, depending on the underlying cause of the issue. All of those errors are identified by the **IFERROR** function, but **IFNA** can only handle** #N/A**. Which one is the better option?

Depending on the circumstances, utilize the **IFERROR** function to handle any form of error. When a formula contains multiple parts that can each cause a different type of error, it is extremely helpful in complex calculations.

Because the** IFNA** function is faster than the **IFERROR** function, you should use it with lookup functions because it does not mask underlying issues with the formula and only provides a custom text when a lookup value cannot be found.

**Conclusion**

We hope that after reading this article, you will be able to use the **IFNA** function in Excel in a variety of appropriate ways. If you have any questions, feel free to comment below.