The IFNA function
Returns a value you choose when a formula gives #N/A, and the formula result otherwise. Other errors still show.
Syntax
IFNA(value, value_if_na)- value
- The formula to check, usually a lookup.
- value_if_na
- What to show instead of #N/A.
Examples
D1
=IFNA(1MATCH("Kiwi",A2:A4,0),20)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 0 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: 1Look for Kiwi in A2:A4, and 2if it is not there (#N/A), show 0 instead.
D1=IFNA(VLOOKUP("Pear",A2:B4,2,FALSE),"Not found")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 6 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |