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.