IFNA Function in Excel and Google Sheets
Purpose
Return a custom result when a formula produces a #N/A error, and the original result otherwise
Return value
value when it does not produce #N/A, value_if_na when the inner formula returns #N/A
IFNA is the surgical alternative to IFERROR when working with lookups. IFERROR catches every error type in one sweep — useful for clean presentation but risky because it also silences genuine formula mistakes. IFNA catches only #N/A, which is the specific error returned when VLOOKUP, MATCH, or INDEX MATCH cannot find what they are looking for. Using IFNA means real formula errors like #REF! or #VALUE! still surface as errors you can see and fix, while the normal case of a lookup value not being in the table is handled gracefully with a fallback. I switched to IFNA as my default lookup wrapper the moment I understood this distinction. It is more precise, more responsible, and in shared workbooks it prevents silent data errors that IFERROR would mask completely.
Syntax
✓ Excel 2013+
✓ Google Sheets
= Same syntax
=IFNA(value, value_if_na)
Arguments
| Argument | Required | Description |
|---|---|---|
| value | Required | The formula to evaluate — usually VLOOKUP, MATCH, or INDEX MATCH. If this does not produce #N/A, IFNA returns it unchanged. |
| value_if_na | Required | What to return when value produces #N/A. Can be a text string like Not found, a number like 0, an empty string, or another formula providing a secondary lookup. |
How it works
IFNA evaluates its first argument. If the result is anything other than #N/A — a number, text, date, another error type, or a blank — IFNA passes it through untouched. Only when the first argument returns #N/A does IFNA substitute the fallback. This selectivity is the main advantage over IFERROR: a #REF! from a broken range reference still shows as #REF!, a #VALUE! from a data-type problem still surfaces, and only the expected not-found case from a lookup is cleanly handled. The most common patterns are IFNA(VLOOKUP(...),fallback) and IFNA(MATCH(...),fallback). With XLOOKUP, the fourth argument handles the not-found case natively so IFNA is less frequently needed there. For Excel 2007 and 2010 where IFNA does not exist, replace it with IFERROR and accept that all error types, not just #N/A, will be caught.
Examples
1
Look up each employee's department by ID from an HR table, showing Not found for IDs that do not exist.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lookup ID | Department | Employee ID | Department |
| 2 | EMP-003 | =IFNA(VLOOKUP(A2,$D$2:$E$6,2,FALSE),"Not found") | EMP-001 | Engineering |
| 3 | EMP-099 | Not found | EMP-002 | Marketing |
| 4 | EMP-001 | Engineering | EMP-003 | Sales |
| 5 | EMP-005 | Engineering | EMP-004 | Finance |
| 6 | EMP-005 | Engineering |
Row 2: Sales
— EMP-003 is found in the Employee ID column, so VLOOKUP returns Sales. If the ID is missing, IFNA replaces the #N/A result with Not found.
Wrapping with IFERROR instead would also catch genuine formula errors like a broken range reference, hiding problems you would want to see. IFNA keeps those visible while only handling the expected not-found case.
2
Find the position of each product code in a reference list using MATCH, returning Not in list for missing codes.
| A | B | C | |
|---|---|---|---|
| 1 | Code | Position | Product List |
| 2 | PRD-003 | =IFNA(MATCH(A2,$C$2:$C$6,0),"Not in list") | PRD-001 |
| 3 | PRD-999 | Not in list | PRD-002 |
| 4 | PRD-001 | 1 | PRD-003 |
| 5 | PRD-005 | 5 | PRD-004 |
| 6 | PRD-005 |
Row 2: 3
— PRD-003 is the third item in the list so MATCH returns 3. PRD-999 is not in the list so MATCH returns #N/A and IFNA shows Not in list.
MATCH with 0 as the third argument performs an exact match. The position returned can then feed into INDEX to pull a value from a corresponding column — the INDEX MATCH pattern.
3
Use INDEX MATCH wrapped in IFNA to look up prices from a product table, showing Unknown for missing codes.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Price | Code | Price |
| 2 | LAP-001 | =IFNA(INDEX($E$2:$E$6,MATCH(A2,$D$2:$D$6,0)),"Unknown") | LAP-001 | 999.99 |
| 3 | LAP-999 | Unknown | MOU-001 | 29.99 |
| 4 | MOU-001 | 29.99 | MON-001 | 299.99 |
| 5 | KBD-001 | Unknown | KBD-002 | 79.99 |
| 6 | MON-001 | 299.99 | WEB-001 | 49.99 |
Row 2: 999.99
— LAP-001 is at position 1 in the code column so INDEX returns 999.99 from the price column. LAP-999 and KBD-001 have no match — MATCH returns #N/A and IFNA shows Unknown.
IFNA wraps the entire INDEX MATCH pair. MATCH produces the #N/A when the code is missing, which propagates through INDEX to the outer IFNA. Both missing codes are handled cleanly by a single IFNA wrapper.
Common use cases
1. Replace the #N/A from VLOOKUP when a product code is not in the price list, returning a zero price for calculations
=IFNA(VLOOKUP(A2,$D:$F,3,FALSE),0)
2. Show a blank cell rather than #N/A when an employee ID does not match any HR record
=IFNA(VLOOKUP(A2,HR!$A:$D,3,FALSE),"")
3. Return Not in list from a MATCH formula when a code does not appear in a validation column
=IFNA(MATCH(A2,$G$2:$G$50,0),"Not in list")
4. Chain two lookups so a second table is tried automatically when the first returns not-found
=IFNA(VLOOKUP(A2,Table1!$A:$B,2,FALSE),VLOOKUP(A2,Table2!$A:$B,2,FALSE))
5. Catch not-found from INDEX MATCH without masking genuine formula errors in the range reference
=IFNA(INDEX($E:$E,MATCH(A2,$D:$D,0)),"Unknown")
Common errors
The fallback value is the wrong type for the column
value_if_na returns a text string but the column is used in numeric calculations — SUM or AVERAGE will ignore the text and undercount.
Fix: Use 0 as the fallback for numeric contexts so every cell contributes to calculations. Use an empty string only for display columns where missing values should look blank.
IFNA does not catch a #VALUE! or #REF! error
IFNA only catches #N/A. Other error types pass through unchanged.
Fix: If all error types need suppressing, replace IFNA with IFERROR. To diagnose the error type, remove the IFNA wrapper temporarily and read the raw error from the cell.
#NAME? on the IFNA function itself
IFNA was introduced in Excel 2013 and does not exist in Excel 2007 or 2010. Older versions return #NAME? for unrecognised function names.
Fix: Replace IFNA with IFERROR for backwards compatibility, or ensure the file is only opened in Excel 2013 or newer.
Tips and variations
Prefer IFNA over IFERROR for all lookup wrappers
IFERROR silences every error including genuine formula mistakes. IFNA catches only the expected not-found case while letting real errors surface. I wrap every VLOOKUP and MATCH with IFNA so broken references and data-type problems remain visible in shared workbooks.
=IFNA(VLOOKUP(A2,$D:$F,3,FALSE),"Not found")
Use zero as the fallback when results feed calculations
A lookup result that feeds SUM or AVERAGE needs a numeric fallback. Returning 0 for not-found values means aggregations continue working correctly. Text fallbacks like Not found make those formulas silently undercount.
=IFNA(VLOOKUP(A2,$D:$F,3,FALSE),0)
Chain a second lookup as the fallback for two-table lookups
Put a second VLOOKUP or INDEX MATCH as the value_if_na argument. The first lookup runs against the primary table and only if it returns #N/A does the secondary lookup run against a backup table — no helper columns needed.
=IFNA(VLOOKUP(A2,Primary!$A:$B,2,FALSE),VLOOKUP(A2,Backup!$A:$B,2,FALSE))
Excel vs Google Sheets
Excel vs Google Sheets
IFNA is available in Excel 2013 and newer and in all versions of Google Sheets. It does not exist in Excel 2007 or 2010 — use IFERROR as a substitute in those versions. The syntax and behaviour are identical in Excel 2013+ and Google Sheets.
Related reading
Frequently asked questions
IFNA catches #N/A errors specifically and replaces them with a value you choose. If the formula inside IFNA does not produce #N/A, IFNA passes the original result through unchanged. It is most commonly used to replace the #N/A that VLOOKUP or MATCH returns when a lookup value is not found.
IFERROR catches all error types including #N/A, #VALUE!, #REF!, and #DIV/0!. IFNA catches only #N/A. Use IFNA when you want to handle not-found lookup results while still letting genuine formula errors like #REF! or #VALUE! surface so you can see and fix them. Use IFERROR when you want all errors suppressed for clean presentation.
XLOOKUP has a built-in fourth argument for the not-found case, so wrapping it in IFNA is rarely needed. =XLOOKUP(A2,D:D,E:E,"Not found") handles the not-found case natively. Use IFNA around XLOOKUP only if the fallback result must be computed by a secondary formula rather than a static value.
Any value that makes sense as a fallback. Common choices are: a text string like Not found for display purposes; 0 when a missing lookup should contribute zero to calculations; a blank string when the cell should look empty; or another formula providing a secondary lookup in a different table.
IFNA was introduced in Excel 2013. It does not exist in Excel 2007 or 2010. For those versions, use IFERROR as a substitute. Google Sheets has supported IFNA since its early versions. If your file must open in Excel 2010 or earlier, replace IFNA with IFERROR throughout.