Google Sheets IFERROR: clean up lookup errors safely

A single missing SKU in a lookup column can scatter #N/A across a dashboard, break every SUM downstream, and turn a shared sheet into a wall of red. The reflex is to wrap the formula in IFERROR and move on. That works — until the day IFERROR quietly swallows a real bug, and the number you present in a meeting has been wrong for a week.

This guide covers the exact IFERROR patterns that keep a Google Sheet clean without hiding the errors you actually need to see. It includes when IFNA is the safer choice, how IFERROR pairs with FILTER and QUERY, and the mistakes that cost you hours later.

What IFERROR actually returns

IFERROR takes two arguments: the formula you want to run, and the value to show if that formula throws any error. If the first argument works, you get its result. If it errors, you get the second argument. If you omit the second argument, IFERROR returns an empty string.

=IFERROR(VLOOKUP("SKU-999", A2:D50, 2, FALSE), "Not found")

The VLOOKUP looks for SKU-999 in the first column of A2:D50 and returns the value from column 2. When SKU-999 does not exist, VLOOKUP returns #N/A, and IFERROR swaps in the string "Not found". The formula never crashes and the cell reads as intended.

Here is the same idea laid out on a small sheet. Row 1 holds headers, row 2 shows the wrapped lookup, row 3 shows what happens when the lookup value is missing:

Google’s own function reference notes that IFERROR is logically equivalent to IF(NOT(ISERROR(value)), value, value_if_error) — a useful mental model when you need to reason about which branch actually runs.

The bug IFERROR hides — and how to catch it

IFERROR does not care which error it caught. It treats #N/A, #REF!, #NAME?, #DIV/0!, #VALUE!, and #NUM! as one blob and swaps them all for your fallback. That is the whole problem.

Say you wrap a lookup, someone later deletes the source column, and now VLOOKUP returns #REF! instead of #N/A. Your IFERROR politely hides it. The cell shows “Not found” for every row, and you assume the SKUs are missing when the sheet itself is broken.

=IFERROR(VLOOKUP(A2, Sheet2!A:D, 3, 0), "Not found")

=IFNA(VLOOKUP(A2, Sheet2!A:D, 3, 0), "Not found")

The debug workflow is simple: write the raw formula first, watch what it returns for real data, and only wrap it in IFERROR after you have seen every legitimate error type it can produce. If you cannot say out loud which error you are catching, do not wrap yet.

IFNA vs IFERROR — pick the right one for lookups

The one-line rule: use IFNA for lookup functions, use IFERROR for everything else. Lookups have exactly one expected failure mode — the value is not there, and Sheets returns #N/A. Every other error from a lookup means the formula itself is wrong, and you want to see that.

IFNA has the same signature: IFNA(value, value_if_na). The only difference is scope. Reach for it whenever the raw formula is a lookup, and reserve IFERROR for cases where more than one error type is a normal outcome. This is the same pattern that keeps text-cleanup formulas readable — narrow catches beat broad ones.

Wrapping FILTER and QUERY without losing information

FILTER and QUERY are Google-Sheets-only functions that fail in ways VLOOKUP never does. FILTER throws #N/A the moment no rows match the criteria, which is normal — an empty result set is still a valid result. QUERY throws parse errors, argument errors, and #REF! when the source range shifts. You want the empty-result case swallowed and everything else visible.

=IFERROR(FILTER(Orders!B2:D, Orders!A2:A = A2), "No orders")

This returns the matching rows from Orders!B2:D where column A equals the value in A2. When no rows match, FILTER returns #N/A and IFERROR shows “No orders” instead of an error. See the FILTER multi-condition guide for the shape of the criteria argument.

For QUERY, wrap only the outer call, never the SQL string. That way an argument typo still surfaces:

The IFERROR here handles the “no matching region” case with a clean zero. If the SQL string has a typo, QUERY still throws a parse error and you know to fix it. The QUERY function guide covers the SQL syntax in detail.

IFERROR inside ARRAYFORMULA

ARRAYFORMULA spreads a formula down a column. When one of the input rows errors, the whole spilled range can look wrong or stop rendering below that row. Wrapping the inner formula in IFERROR keeps the array clean and lets the good rows render.

=ARRAYFORMULA(IFERROR(VALUE(A2:A100), 0))

This converts every value in A2:A100 to a number, substituting zero when the source cell has text that will not parse. Without the IFERROR wrap, a single #VALUE! in row 47 leaves you staring at 53 broken cells. The wrap goes inside ARRAYFORMULA, not outside — otherwise IFERROR only sees the first cell of the range.

Common mistakes and how to spot them

Most IFERROR bugs are subtle because the formula still returns a number. The cell looks fine; the number is silently wrong. Run through this list before you ship a sheet with more than a handful of IFERROR wraps:

• ✓ Removed the IFERROR wrap once, and confirmed the naked formula returns exactly one expected error type
• ✓ Used IFNA (not IFERROR) around every VLOOKUP, XLOOKUP, MATCH, and INDEX/MATCH
• ✓ Fallback value is a real signal — a zero, a blank, or a distinct string — never the empty string when zero would be counted
• ✓ No IFERROR nested more than two levels deep; anything deeper wants IFS or a LAMBDA
• ✓ ARRAYFORMULA-wrapped IFERROR sits inside the array, so it runs per row
• ✓ When a downstream SUM matters, blank fallback ("") instead of a string that SUM would ignore silently

The last one bites hardest. If you wrap a lookup with "Not found" as the fallback, any SUM on that column skips those rows without warning. Switch to a blank fallback or a zero when the column feeds a total, and reserve the string fallback for columns a human reads.

When to stop nesting IFERROR

Two levels of IFERROR is a reasonable pattern — try the primary source, fall back to a secondary source, otherwise show a default:

=IFERROR(VLOOKUP(A2, Primary!A:B, 2, 0),
   IFERROR(VLOOKUP(A2, Backup!A:B, 2, 0), "Unknown"))

Beyond two, the formula becomes hard to read and even harder to debug. Every branch runs quietly; you cannot tell from the cell which source produced the value. The refactor is to pull the choice out with IFS, or to use LET so each branch has a name.

The other exit hatch is a hidden helper column. Put each lookup attempt in its own cell, then a single IFS or COALESCE-style pick at the end. It costs a column, but the sheet stays debuggable, and a future coworker can trace where any given value came from without reverse-engineering a five-deep IFERROR.

A working checklist for a clean sheet

Start by opening the sheet and writing every lookup naked. Watch the errors that appear, and note which error type belongs to which formula. Only then decide what to wrap.

1. Write the raw formula. Do not wrap anything yet.
2. Populate it against real data and identify which error types actually appear.
3. For each error type, decide: is it a normal outcome, or a formula bug I want to see?
4. Wrap only the normal outcomes — IFNA for lookups, IFERROR when multiple error types are normal.
5. Pick a fallback that will not silently corrupt downstream aggregations.
6. Re-read every IFERROR before you ship. If you cannot name the error it is catching, delete the wrap.

IFERROR is a formatting decision, not an error-handling strategy. Treat it that way — narrow, deliberate, and only after you have watched the raw formula fail — and a shared sheet stops surprising you.