# IFNA function in Excel

> IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing.

Source: https://spreadsheetpractice.com/functions/ifna · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing.
- **Syntax:** `=IFNA(value, value_if_na)`
- **Most common use:** `=IFNA(VLOOKUP(D2,A2:B4,2,FALSE),0)` returns 0 (WINTER5 isn't a valid code, so the discount falls back to 0)
- **Available in:** Excel 2013 and later
- **Category:** [Logical functions](https://spreadsheetpractice.com/functions/category/logic)
- **Returns:** The result of value, or value_if_na when it is #N/A. Other errors pass through unchanged.
- **Google Sheets:** Available in Google Sheets.

## What it does

IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing. Unlike IFERROR, it lets every other error through, so a typo or a broken range still shows up instead of hiding behind "Not found".

## Syntax

`=IFNA(value, value_if_na)`

| Argument | Required? | What it is |
|---|---|---|
| `value` | Required | The formula to evaluate, usually a lookup. |
| `value_if_na` | Required | What to return when value is #N/A. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=IFNA(VLOOKUP(D2,A2:B4,2,FALSE),0)` | 0 | WINTER5 isn't a valid code, so the discount falls back to 0 |
| E3 | `=IFNA(VLOOKUP("VIP20",A2:B4,2,FALSE),0)` | 20% | A valid code returns its discount |
| E4 | `=IFNA(VLOOKUP("VIP20",A2:B4,5,FALSE),0)` | #REF! | A wrong column number gives #REF!, and IFNA lets that error through so you notice |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Code | Discount |  | Code entered | Result |
| 2 | SPRING10 | 10% |  | WINTER5 | 0 |
| 3 | VIP20 | 20% |  |  | 20% |
| 4 | FREESHIP | 0% |  |  | #REF! |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| Expecting IFNA to catch #DIV/0! | IFNA only catches #N/A. For division by zero use IFERROR, or test the divisor with IF. |
| A typo gets the fallback | A misspelled lookup value also returns #N/A. Return "Not found" rather than 0 when people need to know a value was missing. |
| #NAME? | IFNA arrived in Excel 2013. In older versions use IF(ISNA(...)) or IFERROR. |

## Tips

- Return a message such as "Check code" instead of 0 when a missing match should be investigated.

## Related functions

- [IFERROR](https://spreadsheetpractice.com/functions/iferror): IFERROR returns a value you choose when a formula results in an error, and the formula's normal result otherwise.
- [ISNA](https://spreadsheetpractice.com/functions/isna): ISNA returns TRUE only for the #N/A error, the one MATCH, VLOOKUP and XLOOKUP return when a value isn't found.
- [VLOOKUP](https://spreadsheetpractice.com/functions/vlookup): VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row.
- [XLOOKUP](https://spreadsheetpractice.com/functions/xlookup): XLOOKUP searches one column or row for a value and returns the matching item from another.
- Comparison: [IFERROR vs IFNA](https://spreadsheetpractice.com/functions/compare/iferror-vs-ifna)

## Questions

### When should I use IFNA instead of IFERROR?

Around lookups. #N/A means the value wasn't found, which is expected. Other errors usually mean the formula itself is broken and should stay visible.

### Does XLOOKUP need IFNA?

No. XLOOKUP's fourth argument, if_not_found, does the same job.

## Practice

A graded IFNA exercise ("Line totals with missing SKUs") is on [the IFNA page](https://spreadsheetpractice.com/functions/ifna). It is checked instantly, then re-checked on changed data.

Lessons that use IFNA:

- [Lesson 42: Handling errors on purpose](https://spreadsheetpractice.com/learn/errors-iferror-ifna) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
