Dealing with Errors in Google Sheets

August 7th, 2026

Every serious spreadsheet hits red error cells eventually: a lookup that finds nothing, a division by zero, a value that is not a number. Google Sheets has a small, deliberate toolkit for this — IFERROR, IFNA, ISERROR, ISERR, ISNA, and ERROR.TYPE. Used well, they keep dashboards readable and pipelines resilient. Used carelessly, they hide bugs you still need to fix. This guide covers what each function does and the patterns that actually hold up in production sheets.

What the main error values mean

You will see these most often:

| Error | Typical cause | | --- | --- | | #N/A | Lookup found no match (XLOOKUP, VLOOKUP, MATCH) | | #DIV/0! | Division by zero or empty denominator | | #VALUE! | Wrong type (text where a number was expected) | | #REF! | Broken reference (deleted row/column, bad range) | | #NAME? | Misspelled function name or undefined named range | | #NUM! | Number out of domain (log of a negative, invalid params) | | #ERROR! | Parse/formula structure problem |

Blanks are not errors. An empty cell and #N/A behave differently in lookups, charts, and arithmetic — treat them as separate cases.

IFERROR — catch any error, return a fallback

IFERROR evaluates a formula and, if that formula returns any error, substitutes a value you choose:

=IFERROR(A2/B2, 0)
=IFERROR(XLOOKUP(E2, A:A, B:B), "Not found")

The first returns 0 when B2 is zero or blank in a way that causes #DIV/0!. The second shows a friendly label when the lookup misses. Prefer a meaningful fallback (0, "", "Not found") over a silent lie when the downstream math needs to know something failed.

IFNA — only catch #N/A

IFNA is narrower: it replaces #N/A and leaves every other error alone.

=IFNA(XLOOKUP(E2, A:A, B:B), "")

That is usually what you want for lookups. A missing key is expected; a #REF! or #VALUE! inside the same formula is not. Wrapping everything in IFERROR would hide the real bug.

Compare:

=IFERROR(1/0, "hidden")
=IFNA(1/0, "hidden")

IFERROR returns "hidden". IFNA still returns #DIV/0! because the error is not #N/A.

ISERROR, ISERR, ISNA — test without swallowing

Sometimes you need a true/false check rather than a replacement value.

• ISERROR — TRUE for any error
• ISERR — TRUE for any error except #N/A
• ISNA — TRUE only for #N/A

=ISERROR(A2/B2)
=ISNA(XLOOKUP(E2, A:A, B:B))
=IF(ISERR(C2), "Fix formula", C2)

These pair well with IF when the “happy path” and the “error path” need different calculations, not just a static label.

ERROR.TYPE — branch on which error it is

ERROR.TYPE returns a number for the error in a cell (and #N/A if the cell is not an error):

| Code | Error | | --- | --- | | 1 | #NULL! | | 2 | #DIV/0! | | 3 | #VALUE! | | 4 | #REF! | | 5 | #NAME? | | 6 | #NUM! | | 7 | #N/A |

=IFERROR(
  SWITCH(ERROR.TYPE(A2),
    2, "Division by zero",
    3, "Wrong type",
    7, "Missing match",
    "Other error"),
  A2)

Use this when a single status column should explain why a row failed, not only that it failed.

Practical patterns

Wrap XLOOKUP (prefer IFNA)

=IFNA(XLOOKUP(E2, Products!A:A, Products!B:B), "Unknown SKU")

If you need a default numeric price of zero for missing products:

=IFNA(XLOOKUP(E2, Products!A:A, Products!C:C), 0)

For a deeper tour of lookup design, see XLOOKUP in Google Sheets: The Future of Data Lookup.

Divide-by-zero without hiding other problems

=IF(B2=0, "", A2/B2)

or, if blank denominators should also yield blank:

=IF(OR(B2=0, B2=""), "", A2/B2)

IFERROR(A2/B2, "") also works, but the explicit IF documents intent and will not mask a #VALUE! from bad inputs in A2.

Blank vs error

=IF(A2="", "Missing input", IFERROR(VALUE(A2), "Not a number"))

Empty cells are often valid “no data yet.” Errors mean “something is wrong.” Collapsing both into the same label makes audits harder.

Defensive pipeline for messy imports

=IFERROR(VALUE(TRIM(A2)), )

That trims, coerces to a number, and returns blank on failure. Combine with the conversion functions in Data Conversion in Google Sheets: N, VALUE, and Related Functions when imports arrive as text.

Don't swallow errors blindly

• Dashboard display vs calculation layer. It is fine to show "—" on a scorecard. It is dangerous to wrap a core calculation so thoroughly that totals still “look right” while half the rows failed.
• Prefer IFNA for lookups. Expected misses are #N/A; unexpected errors should still surface.
• Don't paper over bad source data. If IFERROR fires on every row, clean the import — see Data Cleaning, Sorting, and Analysis Tips in Google Sheets.
• Watch array formulas. One silent error inside an ARRAYFORMULA can cascade; test a single row before wrapping the whole column.

Which tool should you pick?

| Goal | Function | | --- | --- | | Replace any error with a fallback | IFERROR | | Replace only #N/A (lookups) | IFNA | | True/false: is this any error? | ISERROR | | True/false: error but not #N/A? | ISERR | | True/false: is this #N/A? | ISNA | | Custom message per error kind | ERROR.TYPE + IF / SWITCH |

Going further

Error handling pairs naturally with clean inputs and clear logic. For multi-condition checks, see How to Use Logical Functions in Google Sheets: AND, OR, NOT. For function reference pages, open IFERROR, IFNA, ISERROR, ISERR, ISNA, and ERROR.TYPE.