SheetsdrillIFERROR
IFERROR(value, [value_if_error]) returns the value when it calculates normally and the second argument when it produces any error. With one argument it returns a blank, which makes it the quickest way to clean up #N/A from lookups and #DIV/0! from division.
Syntax
=IFERROR(value, [value_if_error])| Part | What it is |
|---|---|
value | The formula or cell that might error. |
value_if_error | Optional. What to show instead; a blank cell if omitted. |
Examples
=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not found")Result: The price, or Not found when A2 is not in the table
=IFERROR(B2/C2, 0)Result: 0 instead of #DIV/0! when C2 is zero or empty
=IFERROR(VALUE(A2))Result: The number in A2, or blank when A2 is not numeric text
Common mistakes
- Wrapping an entire formula in IFERROR hides real mistakes such as a wrong range; use it only around the part that is expected to fail, and use IFNA when you only want to catch missing lookups.
- IFERROR(..., 0) in a column that feeds averages pulls the average down; use "" so AVERAGE skips the cell.
- Nesting IFERROR inside itself to try fallbacks works (=IFERROR(lookup1, IFERROR(lookup2, "None"))) but gets hard to read; XLOOKUP with missing_value is cleaner.
Practice IFERROR
These drills check your answer on live data, free, no account needed.
- A friendly "Not found"Look up the discount for the code in E1. When the code does not exist, show the text "Not found" instead of an error. Answer in E2 with XLOOKUP.
Questions
What is the difference between IFERROR and IFNA?
IFNA only catches #N/A (a value not found); IFERROR catches every error type, including typos and bad ranges.
How do I show a blank instead of an error?
=IFERROR(formula) with no second argument, or =IFERROR(formula, "").
Related
- IFNAHow the IFNA function in Google Sheets replaces only the #N/A error from lookups, why it is safer than IFERROR, with examples for VLOOKUP and MATCH.
- VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.
- IFHow the IF function in Google Sheets returns one value when a condition is true and another when it is false, with examples for text, blanks and nested IFs.
- XLOOKUPHow XLOOKUP works in Google Sheets: look up in any direction, set a not-found value, do approximate matches and search from the bottom, with common errors.