IFNA function
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".
- Available in
- Excel 2013 and later
- Category
- Logical functions
- 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.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| 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 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Code | Discount | Code entered | Result | |
| SPRING10 | 10% | WINTER5 | 0 | |
| VIP20 | 20% | 20% | ||
| FREESHIP | 0% | #REF! |
Practice IFNA
Graded instantly, then re-checked on changed data.
Line totals with missing SKUs
F2, return the line total: the SKU's price from the list times the quantity, or 0 when the SKU isn't in the price list. Use IFNA, then fill down to F5.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.
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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.