How to Use the IFNA Function in Google Sheets
=IFNA(value, value_if_na). Other errors are left alone, which makes it stricter than IFERROR.
What IFNA does
#N/A means "not available". A lookup that cannot find its key returns it, as in the VLOOKUP #N/A guide. IFNA swaps that one error for a friendlier result, such as "Not found".
IFNA syntax
=IFNA(value, value_if_na)
value: the value or formula to check for an #N/A error.value_if_na: what to return ifvalueis #N/A.
Per Google's help page, if value is a range, IFNA returns one result for each cell in that range. The page's examples show IFNA(205, "Na error") returning 205, and IFNA(#N/A, "Na error") returning Na error.
A step-by-step example
Here is a small price list in cells A1:B4, and a product to look up in cell D2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Look up | |
| 2 | Pen | 2 | Hat | |
| 3 | Book | 12 | ||
| 4 | Bag | 25 |
=VLOOKUP(D2, A2:B4, 2, FALSE)
Hat is not in the list, so this returns #N/A. Wrap it:
=IFNA(VLOOKUP(D2, A2:B4, 2, FALSE), "Not found")
This returns Not found. A product that is in the list still returns its price.
IFNA vs IFERROR
IFNA catches only #N/A. In the help page's range example, a cell that divides by zero still shows an error, because it is not #N/A. That is useful: a real mistake in your formula stays visible instead of being hidden. IFERROR catches every error type.
Common mistakes
- Hiding real problems. If you want other errors to show, prefer IFNA over IFERROR.
- Forgetting the quotation marks around a text replacement.
- Expecting it to fix #DIV/0! or #VALUE!. It only handles #N/A.
Try it yourself
Use the table above for exercise 1. Type your formula into the box, starting with =, then press Check.
Exercise 1: Friendlier lookup
Write a formula that looks up D2 in A2:B4 (exact match, column 2) and shows Not found when the result is #N/A.
Exercise 2: Direct #N/A
Write a formula that returns the text Na error when the value is the error #N/A.
What to learn next
- IFERROR: replace an error with a clean result.
- VLOOKUP #N/A error: the causes and the fixes.
- VLOOKUP: fetch one value from a table.
Syntax and rules in this guide were checked against Google's IFNA help page. See all guides.