IFERROR returns a formula’s normal result when it works. When that formula returns any error, IFERROR replaces it with text, zero, a blank, or another formula.
Use it for a failure you expect and understand. For missing lookups, IFNA is often safer because it catches only #N/A.
Replace an Expected Error With IFERROR
Enter this tested source data in A1:B3:
| Amount | Quantity |
|---|---|
| 100 | 4 |
| 100 | 0 |
The test divided each amount by its quantity:
=IFERROR(A2/B2,"Check quantity")With 100 and 4, the formula returned 25. With 100 and 0, it returned Check quantity instead of #DIV/0!.
The fallback communicates what the reader should inspect. Returning zero could wrongly imply that the calculated value is genuinely zero.
IFERROR Function Syntax
=IFERROR(value,[value_if_error])- value is the formula or expression to evaluate.
- value_if_error is the fallback returned for any error. It is optional.
When value succeeds, IFERROR passes its result through unchanged. A legitimate zero, FALSE, or blank is not replaced.
Return a Blank Instead of an Error
Omitting the second argument returns a blank for an error:
=IFERROR(A3/B3)The tested zero-denominator row displayed nothing with this formula.
You can also supply an explicit empty string:
=IFERROR(A3/B3,"")Use IFNA for Missing Lookups
A missing VLOOKUP normally returns #N/A. IFNA can replace that expected result while leaving other errors visible.
The tested lookup table in F1:G3 contained P01/12 and P02/18.
=IFNA(VLOOKUP("P99",F2:G3,2,FALSE),"Not found")The tested formula returned Not found because P99 was absent from the lookup table.
Read the IFNA guide when a missing match is the only error you intend to handle.
Try a Second Lookup After the First Fails
Nested IFERROR can create an ordered fallback across two lookup tables:
For example, put P01/12 and P02/18 in K2:L3, P03/21 and P04/25 in N2:O3, and the ID to find in Q2.
=IFERROR(VLOOKUP(Q2,K2:L3,2,FALSE),IFERROR(VLOOKUP(Q2,N2:O3,2,FALSE),"Missing"))The second VLOOKUP runs only when the first errors. “Missing” appears only after both lookups fail.
Avoid Hiding Broken References
IFERROR catches every error type. That includes unexpected reference, number, value, and name errors that may reveal a broken formula.
The test used an INDEX row number outside its two-row range. IFERROR replaced that problem with a blank:
=IFERROR(INDEX(G2:G3,5),"")Changing IFERROR to IFNA left the unexpected #NUM! visible. Sheets explained that INDEX parameter 2 was out of range.
Test the unwrapped formula first. Then wrap the smallest expression whose expected failure needs a fallback.
See Google’s IFERROR reference for the documented return logic.
Other Google Sheets articles you may also like