IFNA function
Quick answer
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)
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.
Lessons that use IFNA
Answers that use IFNA
In other languages
IFNA is SI.ND in Spanish Excel and WENNNV in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .