Spreadsheet Practice

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)

ArgumentRequired?What it is
valueRequiredThe formula to evaluate, usually a lookup.
value_if_naRequiredWhat 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.

CellFormulaResultWhat it shows
E2=IFNA(VLOOKUP(D2,A2:B4,2,FALSE),0)0WINTER5 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
CodeDiscountCode enteredResult
SPRING1010%WINTER50
VIP2020%20%
FREESHIP0%#REF!

Practice IFNA

Graded instantly, then re-checked on changed data.

Line totals with missing SKUs

In 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 seeWhy, 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 fallbackA 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 .