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:D9 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.