IFNA
The IFNA function returns the value of the second argument if the first one contains the #N/A error.
If there is no error, the value of the first argument is returned.
This function is useful for replacing error messages with more informative values.
Syntax
IFNA(value, value_if_na)
| Argument | Description | Permitted values |
|---|---|---|
| value | Value checked to find the #N/A error | Formula, function or cell reference |
| value_if_na | Value returned if the “value” argument contains the #N/A error | Any value (number, text, and so forth) |
Examples of use
Verification of the result of the VLOOKUP function calculation
IFNA(VLOOKUP(D2, A:B, 2, FALSE), "Not found")
This formula returns “Not found” if the VLOOKUP function did not find the value from cell D2 in range A:B and returned the #N/A error.
Use in combination with the IF function
=IF(IFNA(VLOOKUP(D2, A:B, 2, FALSE)), "Item not found", VLOOKUP(D2, A:B, 2, FALSE))
If the VLOOKUP function returns the #N/A error, “Item not found” will be displayed; otherwise, the value found will be displayed.
Notes
- The IFNA function only checks for the presence of the #N/A error and does not process other types of errors.
- To check for all types of errors, use the IFERROR function.
- The IFNA function is most often used in conjunction with lookup functions (VLOOKUP, MATCH, HLOOKUP) to process cases where the value being searched for is not found.