Excel IFNA Function ( 3 examples)

Excel IFNA Function

Excel IFNA function returns the value users specify if a formula returns the #N/A error value; otherwise, it returns the result of the formula. The Excel MATCH, VLOOKUP, HLOOKUP, XLOOKUP functions return #N/A error if they do not find the desired match of the values. Users can use the IFNA function to trap and handle the #N/A error.

In this section:

---

1. Syntax
2. Example 1: IFNA with MATCH Function
3. Example 2: IFNA function with VLOOKUP Function
4. Difference between IFNA and IFERROR

---

1. Syntax:

IFNA(value, value_if_na)

where:

value: the argument that is checked for the #N/A error value. Required

value_if_na: The use specified value to return if the formula evaluates to an #N/A error value. Required.

2. IFNA with MATCH Function:

The excel IFNA function can be used with MATCH function. To use the MATCH Function with IFNA, users need to nest the MATCH Function with IFNA in the first argument of IFNA, and in the second argument, users need to specify the text they want to return if error is found. In our example below, the formula is =IFNA(MATCH(L5,H6:H13,0), “Not Found”), which returns “Not Found”, when MATCH function does not find a match.

3. IFNA with VLOOKUP:

Users can use excel IFNA Function with VLOOKUP. To use the VLOOKUP function with IFNA, users need to nest the VLOOKUP function with IFNA Function. In our example below, the formula is =IFNA(VLOOKUP(H8, C9:E14, 3, FALSE), “Not Found”), which returns “Not Found” if no match is found.

4. Difference between IFNA and IFERROR:

As we know, the IFERROR function can handle all the error types such as #N/A, #NAME, #VALUE, #REF, #DIV/0, #NUM, and others, while the IFNA function can handle only the #N/A error. Which function you will use depends on the situation. For dealing with #N/A only, use the IFNA function and with other error types, use IFERROR.

More readings:

1. MS Office Post of IFNA
2. Excel IFERROR Function
3. Excel FALSE Function
4. Excel IF Function
5. Excel SWITCH Function