SheetDo
English
Home / Formulas / IFERROR function
Error handling Excel + Google Sheets

IFERROR function

IFERROR wraps a calculation and returns a fallback when it errors — the fastest way to keep #N/A and #DIV/0! out of a report. Learn where it shines, where it silently hides real problems, then prove it on a live sheet.

IFERROR syntax & arguments

=IFERROR(value, [value_if_error])

IFERROR is the fuse of error handling: if value computes normally it returns the result; the moment it errors, it returns your fallback instead.

1 value
Required

The formula or expression to check (e.g. C2/B2, VLOOKUP(E2, A2:C6, 3, FALSE)). Every error type gets intercepted: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!.

2 value_if_error
Optional

What to return on error: double-quoted text ("Not found"), a number, blank (""), or another formula. If omitted, errors display as empty cells.

Which errors does it catch?

  • #N/A — A lookup can't find the target (VLOOKUP, XLOOKUP, MATCH miss)
  • #DIV/0! — Division by zero or by an empty cell
  • #VALUE! — Wrong argument type, e.g. doing math on text
  • #REF! — A referenced cell was deleted — structural damage; think twice before masking it
  • #NAME? — A function or name is misspelled, e.g. =VLOKUP(…)

The classic pair: safe division & lookup fallback

=IFERROR(C2/B2, "Check qty")

Division is the most common source of #DIV/0!: qty 0 or blank blows it up. Note: a total of 0 is a valid number (0 ÷ 5 = 0) — IFERROR leaves it alone.

=IFNA(VLOOKUP(E2, A2:C6, 3, FALSE), "Not found")

For lookup misses, IFNA is more precise than IFERROR: it converts only #N/A and still lets structural #REF! errors scream. XLOOKUP users can skip wrappers entirely: =XLOOKUP(E2, A2:A6, C2:C6, "Not found").

Three tools, three jobs: IFNA / IFERROR / ISNA

IFNA

The scalpel. Converts only #N/A (a miss) and leaves every other error visible — the first choice for lookup fallbacks, so real damage stays loud.

IFERROR

The fuse. Catches everything, zero effort; the price is that #REF!-class breakage also becomes "Not found". Best as the last line of defense before shipping a report.

ISNA / ISERROR

The auditor. Doesn't replace, only reports: =ISNA(VLOOKUP(…)) returns TRUE/FALSE — perfect for audit columns, conditional formatting, and =SUMPRODUCT(--ISNA(range)) counting.

Common mistakes & gotchas

  • Masking — IFERROR masks everything: =IFERROR(VLOOKUP(…), "") even turns a deleted-column #REF! into blank — the report keeps being wrong, quietly. Prefer IFNA or an explicit IF check so the fuse doesn't swallow the evidence.
  • Syntax errors — It can't save syntax errors: a formula parse error (unbalanced parentheses, missing quote, wrong separator) means the formula never evaluates, so IFERROR never fires. Fix the syntax first.
  • Blanket wrapping — Don't blanket-wrap ranges: IFERROR over a whole range erases the clues about which row broke. To debug, mark rows with ISERROR/ISNA first, fix the data, then wrap.

Troubleshooting: Why Formulas Won't Calculate (or Show As Raw Text)

If Excel or Google Sheets displays your formula as literal text (e.g. showing =SUM(A1:A5) instead of the total) or stops recalculating, IFERROR cannot help because the formula hasn't evaluated. Check these 4 common culprits:

1. Cell formatted as Text (@)

If the cell was set to Text before typing the formula, Excel treats the formula as literal words. Fix: Change format to General (or Number), then press F2 and Enter to force re-evaluation.

2. 'Show Formulas' mode is turned on

If all formulas across the entire sheet are visible simultaneously, press Ctrl + ` (tilde) / Cmd + ` to toggle back to normal value view.

3. Calculation Options set to Manual

Go to Formulas tab → Calculation Options and check if it was set to Manual. Switch it back to Automatic (or press F9 to recalculate immediately).

4. Leading space before the equals sign

If there is a space before the = sign (e.g. " =A1+B1"), Excel treats it as text. Delete the leading space to restore calculation.

IFERROR starter practice: unit prices that survive bad data

Type IFERROR into the locked mini sheet below and watch #DIV/0! become a useful message — or open the standalone exercise.

Open standalone exercise →
Description
Hints 0/4
Solutions 2
–
Test case
1
2
3
Reset
Submit
fx
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
Loading interactive spreadsheet...
Preparing grid cells and formula calculation engine
○ Ready Enter formula in highlighted cells, then click Submit.

Related practice problems

The full error-handling family in action: division by zero, lookup misses, ISNA audits — build the muscle memory on real scenarios.

Medium Logic

Audit missing SKUs with ISNA

Before the price report ships, QA wants a simple audit answer for the SKU in E2: is it missing from the price list in A2:A6 or not? Put TRUE (missing) or FALSE (present) in the Missing? cell D2. This is an audit question — you want the TRUE/FALSE itself, not a wrapped-up message.
Medium Data Cleaning

Handle missing SKUs in a lookup (IFERROR)

Customer service forwarded an order list, and one of the SKUs was discontinued — it is not in the price list anymore, so a plain lookup would blow up with an error in the middle of your report. Fill the Unit price cells F2:F4 so every order row either shows the matched price or the text Not found.
Medium Lookup & Reference

XLOOKUP misses without blowing up (IFERROR)

Customer service keeps typing SKUs that stopped existing, and every miss splatters #N/A across the price report. Look up the price for the SKU in E2 from the list in A2:C6, and return the text "Not found" instead of an error. Fill the Price cell F2 — two ways solve this, and one of them doesn't even need IFERROR.
Medium Lookup & Reference

Default to zero on missing discount (VLOOKUP + IFERROR)

You are processing store checkouts. Members get a discount based on their VIP tier in columns A–B (Bronze 5%, Silver 10%, Gold 15%, Platinum 20%). Non-VIP customers have standard tiers like 'Regular' or 'None' that are NOT in the VIP table, causing a naked VLOOKUP to fail with #N/A. Fill the Discount cells E2:E5 using VLOOKUP wrapped in IFERROR (or IFNA) so missing tiers safely default to 0.
Hard Logic

Calculate tiered sales commission (IF)

Quarter-end is here and sales ops needs commission run for the team in B2:B5. The plan has three tiers: 8% on sales of at least 10,000, 5% on sales of at least 5,000, and 3% below that. Fill the Commission cells D2:D5 with each person's payout.
Easy Logic

Count passing scores (COUNTIF)

You just finished grading an exam for an adult-education class. The passing line is 60 points, and the grade report needs one number at the top: how many of the eight students in B2:B9 passed. Fill the Passed count cell D2.

FAQ

What does IFERROR do in Excel?

=IFERROR(value, value_if_error) evaluates value and returns its result — unless it produces an error (#N/A, #DIV/0!, #VALUE!, #REF!, #NAME?), in which case the fallback is returned instead. If value_if_error is omitted, errors show as blank cells.

What is the difference between IFERROR and IFNA?

IFNA only converts #N/A — lookup misses — and leaves every other error visible. IFERROR converts all errors. For lookups, prefer IFNA (or XLOOKUP's if_not_found argument): a #REF! from a deleted column should alarm you, not silently become "Not found".

How do I fix #N/A in VLOOKUP or XLOOKUP?

Wrap it: =IFNA(VLOOKUP(…), "Not found") converts only misses. XLOOKUP offers a native 4th argument: =XLOOKUP(E2, A2:A6, C2:C6, "Not found"). To audit instead of replace, use =ISNA(VLOOKUP(…)) which returns TRUE for a miss.

Why does Google Sheets show a formula parse error?

A formula parse error means Sheets can't even read the formula — unbalanced parentheses, a missing quote, or a wrong separator (some locales need ; instead of ,). IFERROR can't help: syntax errors stop the formula from evaluating at all. Fix the syntax, then use IFERROR for runtime errors.