How to Use IFERROR in Google Sheets
=IFERROR(value, [value_if_error]).
What IFERROR does
Formulas break: a lookup finds nothing, a division uses zero. IFERROR catches the error and shows something else, so your sheet stays readable. If you leave out the replacement, the cell shows a blank.
IFERROR syntax
=IFERROR(value, [value_if_error])
value: the formula or value to try. If it is not an error, this is what you get.value_if_error(optional): what to show whenvalueis an error. It defaults to blank.
Google's help page says IFERROR(exp1, exp2) is logically the same as IF(NOT(ISERROR(exp1)), exp1, exp2).
A step-by-step example: a failed lookup
Here is a product table in cells A1:C5.
| A | B | C | |
|---|---|---|---|
| 1 | ID | Name | Price |
| 2 | P-101 | Mug | 6.5 |
| 3 | P-102 | Lamp | 24 |
| 4 | P-103 | Backpack | 39 |
| 5 | P-104 | Notebook | 8.75 |
A lookup for an ID that does not exist normally shows #N/A. To show a message instead:
=IFERROR(VLOOKUP("P-999", A2:C5, 3, FALSE), "Not found")
This returns Not found. Here is why:
- Sheets tries the VLOOKUP. There is no P-999, so it produces #N/A.
- IFERROR sees the error and returns its second argument instead.
- If the ID did exist, the price would come through unchanged.
A second example: dividing by zero
Say A2 holds a total of 50 and B2 holds a count of 0.
| A | B | |
|---|---|---|
| 1 | Total | People |
| 2 | 50 | 0 |
=IFERROR(A2/B2, 0)
Dividing by zero is an error, so this returns 0 instead of #DIV/0!.
Common IFERROR mistakes
- Hiding real problems. IFERROR catches every kind of error, including typos in a range. Test the formula without it first.
- Showing 0 when 0 is a real answer. A text message such as
"Not found"is clearer when 0 could be a valid result. - Wrapping too much. Put IFERROR around only the part that can fail.
- Mixing text into math. A text replacement in a column you later sum may cause problems elsewhere.
Try it yourself
Use the tables above. Type your formula into the box, starting with =, then press Check.
Exercise 1: Friendly lookup
Write a formula that looks up "P-999" in A2:C5 and returns the Price (column 3) with an exact match, but shows Not found if the lookup fails.
Exercise 2: Avoid #DIV/0!
Write a formula that divides A2 by B2, but returns 0 if the division is an error.
What to learn next
- VLOOKUP: fetch one value from a table.
- INDEX and MATCH: look up a value in any direction.
- IF: run a test and return one of two results.
- IFNA: replace only #N/A errors.
- ISERROR and ISNA: test for an error without replacing it.
Syntax and rules in this guide were checked against Google's IFERROR help page. See all guides.