What IFERROR Does
IFERROR evaluates a formula and returns its result if there is no error, or a fallback value if there is one. It catches all error types — #N/A, #DIV/0!, #REF!, #VALUE!, and others. It is the standard way to prevent ugly error messages from appearing in your spreadsheet.
Syntax
=IFERROR(value, [value_if_error])
| Parameter | Description |
|---|---|
| value | The formula or expression to evaluate. |
| value_if_error | The value to return if value produces an error. If omitted, returns an empty string. |
Basic Examples
Example 1: Safe Division
Dividing by zero normally returns #DIV/0!. IFERROR catches it:
| A | B | C (Formula) | C (Result) | |
|---|---|---|---|---|
| 1 | Revenue | Deals | Avg Deal Size | Avg Deal Size |
| 2 | 50000 | 10 | =IFERROR(A2/B2, 0) | 5000 |
| 3 | 0 | 0 | =IFERROR(A3/B3, 0) | 0 |
Example 2: VLOOKUP with Fallback
When VLOOKUP can't find a match, it returns #N/A. Wrap it to show a friendly message:
=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not found")
Result: The product price if found, or "Not found" if the product doesn't exist.
Example 3: Return Blank on Error
Sometimes you just want the cell to appear empty:
=IFERROR(A2/B2)
When value_if_error is omitted, IFERROR returns an empty string by default.
Advanced Examples
IFERROR with FILTER
FILTER returns #N/A when no rows match. Handle it cleanly:
=IFERROR(FILTER(A2:C100, B2:B100 = "VIP"), "No VIP customers found")
If no rows have "VIP" in column B, the cell shows "No VIP customers found" instead of an error.
Chaining IFERROR for Fallback Lookups
Try to find a value in one table, and if not found, try a second table:
=IFERROR(VLOOKUP(A2, Table1!A:B, 2, FALSE), IFERROR(VLOOKUP(A2, Table2!A:B, 2, FALSE), "Not in either table"))
The formula first checks Table1. If not found, it checks Table2. If still not found, it returns the fallback text.
Common Mistakes
- Hiding real errors. IFERROR catches all errors, including ones caused by typos or broken references. If your formula has a bug, IFERROR masks it. Use IFERROR only when you know the specific error you expect (like #N/A from a lookup). For #N/A specifically, consider IFNA instead.
- Wrapping entire complex formulas. Placing IFERROR around a large formula makes debugging difficult. Keep formulas simple and only wrap the part that might error.
- Using IFERROR when IF would be better. If you know the error condition (e.g., denominator is zero), test for it directly:
=IF(B2=0, 0, A2/B2). This is more explicit and doesn't mask unexpected errors.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "look up the price, show 'N/A' if not found" — and the formula is generated for you. Install SheetAI to try it free.