IFERROR vs IFNA
IFERROR vs IFNA: which error handler should wrap your formulas? Compare what each one catches, with lookup and division examples for Excel and Google Sheets.
Introduction
IFERROR and IFNA look interchangeable at a glance — both wrap a formula and swap in a fallback when it breaks. The difference is scope: IFERROR catches every error, while IFNA catches only #N/A. Choosing the wrong one either hides genuine bugs in your sheet or leaves avoidable errors on screen. This guide shows exactly what each function traps and which one to reach for.
Prerequisites
- Basic formula syntax
- Familiarity with VLOOKUP or XLOOKUP
- Excel 2007 or later, or Google Sheets
1The One-Line Difference: What Each Function Catches
IFNA traps a single error: #N/A, the value lookups return when they find nothing. IFERROR is a catch-all — it swallows #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!, plus the newer #SPILL! and #CALC! values in current Excel builds. The syntax is nearly identical: IFERROR(value, value_if_error) and IFNA(value, value_if_na). Both arrived in Excel 2007 and both exist in Google Sheets with the same behaviour.
Example
=IFNA(1/0, "n/a")Division by zero is not #N/A, so IFNA does nothing and the error stays visible. IFERROR would have replaced it with "n/a".
Rule of thumb: use IFNA when you expect a lookup to miss; use IFERROR when you expect a calculation to break.
In Google Sheets the second argument is optional for IFERROR and returns blank; Excel requires both arguments.
2IFNA in Practice: Protecting Lookups
Most #N/A errors mean something legitimate — the item simply is not in the list yet. That is a data condition, not a bug, so IFNA is the right wrapper. Wrapping a lookup in IFNA keeps genuine formula problems, such as a broken range or a stray text value, visible so you can fix them, while quietly handling the everyday "not found" case.
Wrap the lookup
Put the whole VLOOKUP inside IFNA and supply the fallback as the second argument.
=IFNA(VLOOKUP(E2, A2:C500, 3, FALSE), "Not listed")Use a neutral fallback for numbers
Return 0 instead of text so downstream sums still work.
=IFNA(VLOOKUP(E2, A2:C500, 3, FALSE), 0)Example
=IFNA(VLOOKUP("K-99", A2:C500, 3, FALSE), "Not listed")Product K-99 is absent from the table, so VLOOKUP returns #N/A and IFNA substitutes the friendly message. The same wrapper works for XLOOKUP and INDEX + MATCH.
XLOOKUP has this built in: =XLOOKUP(E2, A2:A500, C2:C500, "Not listed") needs no wrapper at all.
Return "" rather than 0 if a later AVERAGE should ignore missing rows — AVERAGE skips text but counts zeros.
3When IFERROR Is the Right Choice
Outside lookups you rarely know which error will appear, and that is where IFERROR earns its place. Dividing by a possibly-empty cell, converting text to numbers, or extracting a substring can each fail in several different ways. IFERROR handles them all in one wrapper, and it evaluates the inner expression only once — unlike the older IF(ISERROR(...)) pattern, which computes it twice.
Example
=IFERROR(A2/B2, 0)A blank or zero divisor raises #DIV/0!, and text in either cell raises #VALUE!. IFERROR covers both with a single wrapper.
IFERROR(A2/B2, 0) also guards against #VALUE! if A2 or B2 holds text — IFNA would leave that error on screen.
Prefer IFERROR over IF(ISERROR(x), fallback, x): the latter recalculates x twice, which is slower and can return inconsistent results with volatile functions.
4The Hidden Cost of IFERROR: Masked Bugs
Because IFERROR catches everything, it also catches your mistakes. A mistyped range, a deleted column, or a col_index_num larger than the table all produce errors that IFERROR silently converts into your fallback text. The sheet looks clean while quietly returning wrong answers — the worst kind of bug to track down later.
Example
=IFERROR(VLOOKUP(F2, A2:A10, 2, FALSE), "Not found")The table has only one column but col_index_num is 2, so VLOOKUP returns #REF!. IFERROR disguises a broken formula as missing data. With IFNA the #REF! would surface immediately.
Build and test the inner formula first; only add the IFERROR wrapper once you have confirmed it returns correct results.
During development, temporarily drop the wrapper or use IFNA so real errors stay visible.
Never wrap a formula in IFERROR just to "make the errors go away" — fix the underlying cause instead.
5ISERROR and ISNA: Testing Without Wrapping
Sometimes you do not want a fallback value — you want to know whether an error occurred. ISERROR and ISNA return TRUE or FALSE instead of a substitution, which makes them ideal for conditional formatting, status flags, and counting how many rows failed. ISNA is TRUE only for #N/A; ISERROR is TRUE for any error.
Flag unmatched rows
Combine ISNA with MATCH to mark items that are missing from a second list.
=ISNA(MATCH(A2, D2:D50, 0))Count the failures
Wrap the test in SUMPRODUCT to total the unmatched rows across a range.
=SUMPRODUCT(--ISNA(MATCH(A2:A100, D2:D50, 0)))Example
=SUMPRODUCT(--ISNA(MATCH(A2:A100, D2:D50, 0)))MATCH returns #N/A for each miss, ISNA turns those into TRUE, and the double unary minus converts TRUE/FALSE to 1/0 so SUMPRODUCT can add them up.
In Excel 365 you can skip SUMPRODUCT and wrap ISNA in SUM — it aggregates arrays natively.
Use =ISERROR(A1) as a conditional formatting rule to highlight every error cell in a report at once.
Google Sheets behaves identically here, but array formulas over a range need ARRAYFORMULA in legacy Sheets.
Functions Used
IFERROR
IFERROR returns a custom result when a formula generates an error, and the formula result otherwise.
IFNA
IFNA returns a specified value when a formula results in the #N/A error, and otherwise returns the formula's own result. It catches only #N/A, not other errors.
ISERROR
ISERROR returns TRUE if the supplied value is any Excel error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!), and FALSE otherwise.
ISNA
ISNA returns TRUE if the supplied value is the #N/A error, and FALSE for any other value or error.
Related Guides
Excel N/A Error Fix
The complete Excel N/A error fix guide: why #N/A appears in VLOOKUP and XLOOKUP, how to repair the root cause, and when to wrap it with IFNA or IFERROR.
Nested If Alternatives
Long nested IF formulas are hard to read and easy to break. Discover the best nested IF alternatives in Excel and Google Sheets — IFS, lookup tables, and XLOOKUP.
IF with AND / OR
Combining IF with AND OR logic lets a single formula test several conditions at once. Learn the syntax, nesting order and Excel and Google Sheets examples.
Summary
Reach for IFNA when the only failure you expect is a lookup that finds nothing — it handles the miss while letting real formula bugs stay visible. Reach for IFERROR when a calculation can fail in unpredictable ways, such as dividing by an empty cell, but build and test the inner formula first so you are not masking a mistake. When you need to detect or count errors rather than replace them, ISNA and ISERROR give you a TRUE/FALSE answer that works in status flags, conditional formatting, and SUMPRODUCT totals.
Next Steps
- Audit your existing sheets: replace IFERROR with IFNA around lookups so genuine errors surface again
- Use SUMPRODUCT with ISNA to reconcile two lists and count what is missing
- Pair these tests with IF and AND / OR to build multi-condition error handling