IFNA in Excel
IFNA(value, value_if_na) returns value unless it is the #N/A error, in which case it returns value_if_na. Wrap it around VLOOKUP, MATCH or XLOOKUP to handle a missing key while still seeing real mistakes such as #REF! or #VALUE!.
Syntax
IFNA(value, value_if_na)| Part | What it is |
|---|---|
value | The formula to check, usually a lookup. |
value_if_na | What to return when the formula gives #N/A. |
Examples
=IFNA(VLOOKUP(E2, A:C, 3, FALSE), "Not found")Result: Not found when E2 is missing from column A, the looked-up value otherwise
=IFNA(MATCH(E2, A2:A100, 0), 0)Result: 0 instead of #N/A when there is no match
=IFNA(XLOOKUP(E2, A:A, C:C), "")Result: An empty cell for a missing key
XLOOKUP has its own fourth argument for this; IFNA is the option for VLOOKUP and MATCH.
=IFNA(VLOOKUP(E2, A:C, 5, FALSE), 0)Result: #REF!
The column number is wrong, and IFNA lets that error through so you can see it. IFERROR would have hidden it behind a 0.
Common mistakes
- Using IFERROR for a missing key hides every other error too: a deleted column, a typo in a range name, a division by zero.
- Returning 0 for a missing key makes a missing price look like a free item in a total. Return text or an empty string if a missing key should stand out.
- IFNA does not catch #DIV/0!, #VALUE! or #REF!. That is the point of it, not a fault.
- Putting IFNA inside the lookup instead of around it. The lookup goes in the first argument.
Practice IFNA
These drills check your answer on live data, free, no account needed.
- A lookup inside IFERRORReturn the price for the code in F1 with VLOOKUP, in F2. When the code is not in the table, the cell must show the text Not found instead of #N/A.
- When the code does not existReturn the stock level for the code in F1, in F2. If the code is not in the list, the cell must show the text Unknown code instead of an error.
- No more #DIV/0!Revenue per unit is revenue divided by units. When units are 0 the cell must show 0 instead of an error. Fill D2 and D3.
Questions
What is the difference between IFNA and IFERROR?
IFNA replaces only #N/A, the error a lookup gives for a missing key. IFERROR replaces every error. Use IFNA around lookups so that real mistakes stay visible.
Which Excel versions have IFNA?
Excel 2013 and later, and Google Sheets.
Do I need IFNA with XLOOKUP?
No. The fourth argument of XLOOKUP is the value to return when nothing is found.
Related
- IFERRORHow the Excel IFERROR function returns a value you choose instead of an error, with examples for division by zero and VLOOKUP, and when IFNA is better.
- VLOOKUPHow to use VLOOKUP in Excel to find a value in the first column of a table and return a value from another column, with exact and approximate match examples.
- XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.
- ISERROR, ISBLANK and IS functionsHow the Excel IS functions (ISBLANK, ISERROR, ISNA, ISNUMBER, ISTEXT) return TRUE or FALSE about a cell, with examples inside IF and the empty string trap.