IFERROR function
IFERROR returns a formula's result, or a friendlier value such as blank or zero if that formula produces an error. It is common around lookups and divisions.
Example
Read it as a sentence: show F1 unless it is an error, in which case show x. F1 is empty, so the result is 0.
Excel shows 0
F
1
Data
Syntax
IFERROR(value, value_if_error)
-
value(required) - The formula or value to try.
-
value_if_error(required) - What to return if value produces any error.
Returns: value, or value_if_error if value is an error.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=IFERROR(1/0,"err") | err | Error replaced |
=IFERROR(5,"err") | 5 | Value passes |
=IFERROR(NA(),"x") | x | N/A replaced |
=IFERROR(1/0,) | 0 | Empty replacement |
=IFERROR({1,#N/A,3},"x") | 1 | Array spills |
=IFERROR(Data!F1,"x") | 0 | Blank value |
=IFERROR(1/0,1/0) | #DIV/0! | Replacement is an error |
=IFERROR(LAMBDA(x,x),"x") | #CALC! | Uncalled lambda |
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
Data
Watch out for
- It hides every error type, including real mistakes like bad references; prefer IFNA when only #N/A is expected.
- Substituting 0 for errors can quietly distort totals.
Where it works
- Excel
- IFERROR is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .