Syntax
=IFNA(value, value_if_na)
value- The formula to try (usually a lookup).
value_if_na- What to show if the result is #N/A.
IFNA function in Google Sheets
IFNA catches only the #N/A error, the one lookups return when a value isn't found. Every other error still shows, so real mistakes don't get hidden.
Practice IFNA
Task 1 of 1
Look up the price for the SKU in D2. If the SKU isn't listed, show "Not listed", but use IFNA so that any other kind of error still shows up. Answer in E2.
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
IFNA examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Emp ID | Name | Dept | Salary | |
| 2 | 1042 | Maya Patel | Finance | maya.patel@acme.co | $72,000.00 |
| 3 | 1187 | Leo Rossi | Support | leo.rossi@acme.co | $51,000.00 |
| 4 | 1305 | Ava Kim | Sales | ava.kim@acme.co | $64,000.00 |
| 5 | 1411 | Omar Ali | Marketing | omar.ali@acme.co | $58,000.00 |
| 6 | 1520 | Hana Tanaka | Sales | hana.tanaka@acme.co | $69,000.00 |
| Formula | Result | What it shows |
|---|---|---|
=IFNA(VLOOKUP(9999, A2:B6, 2, FALSE), "No match") | No match | Not found becomes "No match". |
=IFNA(MATCH("Sales", C2:C6, 0), 0) | 3 | Found at position 3, so the normal result comes through. |
=IFNA(1/0, "x") | #DIV/0! | #DIV/0! isn't #N/A, so it is not caught. |
Tips and common mistakes
- XLOOKUP has a built-in missing_value argument, so you don't need IFNA around it.
Related functions
Learning step by step? IFNA is covered in Lesson 9: IFERROR & IFNA of the free course.