SheetsdrillIFNA
IFNA(value, value_if_na) returns the value unless it is the #N/A error, in which case it returns the second argument. It is the precise way to handle a lookup that finds nothing, while still letting other errors such as #REF! or #DIV/0! show so you can fix them.
Syntax
=IFNA(value, value_if_na)| Part | What it is |
|---|---|
value | The lookup or formula that may return #N/A. |
value_if_na | What to return instead of #N/A: a number, text or blank "". |
Examples
=IFNA(VLOOKUP(A2, Points!A:B, 2, FALSE), 0)Result: The points for the member in A2, or 0 when they are not in the table
=IFNA(MATCH(A2, List!A:A, 0), "New")Result: The position in the list, or New
=SUM(IFNA(VLOOKUP(A2:A10, Points!A:B, 2, FALSE), 0))Result: Total points for ten members, missing ones counted as 0
Press Ctrl+Shift+Enter or wrap in ARRAYFORMULA.
Common mistakes
- Using IFNA on a division or a typo does nothing because those errors are not #N/A; use IFERROR, or better, fix the formula.
- Replacing #N/A with 0 in a lookup that returns text mixes types in the column; use "" or a label for text results.
- Forgetting the second argument gives a wrong-number-of-arguments error; unlike IFERROR, IFNA requires both.
Practice IFNA
These drills check your answer on live data, free, no account needed.
- Zero instead of #N/ALook up the loyalty points for the member in E1. Members who are not on the list have 0 points. Use IFNA around an exact VLOOKUP. Answer in E2.
Questions
When should I use IFNA instead of IFERROR?
Whenever the only error you expect is a value not found. IFNA lets a broken range or bad argument still show an error instead of silently hiding it.
Does XLOOKUP need IFNA?
Not usually, because XLOOKUP has its own missing_value argument. IFNA is for VLOOKUP, HLOOKUP, MATCH and INDEX/MATCH.
Related
- IFERRORHow IFERROR in Google Sheets hides errors like #N/A, #DIV/0! and #VALUE! with a blank, a zero or a message, with examples for VLOOKUP and division.
- VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.
- INDEX and MATCHHow INDEX and MATCH work together in Google Sheets to look up values left or right, do two-way lookups by row and column, and find the last match.
- XLOOKUPHow XLOOKUP works in Google Sheets: look up in any direction, set a not-found value, do approximate matches and search from the bottom, with common errors.