IFNA replaces an #N/A result with a message, number or another formula. Other error types remain visible. Wrap the formula that produces the error, then supply the fallback you want to show.
IFNA function syntax
=IFNA(value, value_if_na)- value: the expression or cell to check for #N/A.
- value_if_na: the replacement, such as “Not found”, 0 or a second lookup.
- Use IFERROR only when you intend to handle other error types too. IFNA does not repair an incorrect lookup that returns a plausible value.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Code | Product | Search |
|---|---|---|
| P01 | Keyboard | P03 |
| P02 | Mouse | P99 |
| P03 | USB Hub | P01 |
| P04 | Stand | P88 |
Replace a missing VLOOKUP result
Enter this formula in A8. It searches for P99, which is absent from A2:A5, and returns Not in catalog. Replace “P99” with C2 to look up the first search code instead.
=IFNA(VLOOKUP("P99",A2:B5,2,FALSE),"Not in catalog")Result: Not in catalog.
Let a successful lookup pass through
C2 contains P03. The lookup finds its product, USB Hub, and IFNA leaves that result unchanged. Lock the catalog range if you plan to fill the formula down.
=IFNA(VLOOKUP(C2,$A$2:$B$5,2,FALSE),"Not in catalog")Result: USB Hub.
Handle an unknown MATCH lookup
MATCH returns a position inside the searched range. P03 is the third code in A2:A5, so this returns 3. A missing code returns the fallback label instead.
=IFNA(MATCH(C2,$A$2:$A$5,0),"Not on list")Result: 3.
Use IFNA with XLOOKUP
The same wrapper works around XLOOKUP. This searches P99 and returns Unknown product. For missing keys alone, XLOOKUP also has its own fourth argument for the fallback.
=IFNA(XLOOKUP("P99",A2:A5,B2:B5),"Unknown product")Result: Unknown product.
Handle a whole list with ARRAYFORMULA
This version processes C2:C5 and returns four results in order. Enter it once in an empty area; there is no need to fill the formula down.
=ARRAYFORMULA(IFNA(VLOOKUP(C2:C5,A2:B5,2,FALSE),"Not in catalog"))Result: USB Hub; Not in catalog; Keyboard; Not in catalog.
Try a backup catalog before showing a message
Enter the backup table at A20. P88 is absent from the main catalog but present in the backup. The outer IFNA tries the second lookup only when the first result is #N/A.
| Backup code | Product |
|---|---|
| P88 | Cable |
| P90 | Webcam |
=IFNA(VLOOKUP("P88",A2:B5,2,FALSE),IFNA(VLOOKUP("P88",A21:B22,2,FALSE),"Product unknown"))Result: Cable.
Keep other errors visible
Dividing by zero produces #DIV/0!, which IFNA does not replace. Fix the calculation rather than treating every error as a missing lookup.
=IFNA(1/0,"Missing")Result: #DIV/0!.
Other Google Sheets articles you may also like