Arguments
value- The formula or expression to evaluate.value_if_error- What to return if value is any error (#N/A, #DIV/0!, etc.).
IFERROR
Returns a fallback value when a formula produces an error, otherwise the formula's result.
Syntax
=IFERROR(value, value_if_error)=IFERROR(VLOOKUP(A2, D:F, 3, FALSE), "Not found")Returns: The lookup, or "Not found" instead of #N/A
The tidy way to hide lookup misses.
=IFERROR(A2/B2, 0)Returns: A2/B2, or 0 when B2 is blank/zero
Avoids #DIV/0! in calculated columns.
How IFERROR evaluates
IFERROR evaluates its first argument, checks whether the result is any error value, and returns the second argument if it is. It catches all seven of them - #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL! - with no way to select among them. That breadth is its convenience and its risk in one: the same wrapper that hides an expected lookup miss also hides a #REF! left behind by a deleted column.
It is not a repair. The first argument is still evaluated in full; IFERROR only decides what to display afterwards, so the cost of a slow formula is unchanged and a broken one stays broken behind the fallback. When the fallback is a formula of its own, that second formula is evaluated too, which is how =IFERROR(VLOOKUP(...), VLOOKUP(...)) ends up doing two lookups on every row.
For the common case of a lookup that may legitimately find nothing, IFNA is the better tool: it replaces only #N/A and leaves every other error visible. XLOOKUP's if_not_found argument is better still, because it never evaluates a second lookup and never touches errors coming from the data. Reach for IFERROR when you genuinely mean any error at all - a division whose denominator may be blank, or an import where several failure modes all mean the same thing.
- Catches every error type
- #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL! are all replaced. #SPILL! and #CALC!, added with dynamic arrays, are caught too. There is no argument to restrict which ones.
- The fallback is returned as-is
- It is not coerced to the type of the first argument. =IFERROR(A1/B1, "-") produces a text hyphen in a numeric column, which then breaks a SUM over that column unless the fallback is 0 instead.
- Both arguments are evaluated
- There is no short-circuiting: Excel computes the value and the fallback, then chooses. A second lookup as the fallback therefore doubles the work on every row, whether or not the first one succeeded.
- Errors inside the fallback are not caught
- If value_if_error itself errors, that error is what you get. Nesting IFERROR calls is the usual answer, and is also the point at which a helper column becomes more readable.
- It works over arrays
- Given an array, IFERROR tests element by element and returns an array with the failing elements replaced. In Excel 2021 and 365 that spills; in earlier versions it must be array-entered or consumed by an aggregate.
- A blank fallback is not a blank cell
- Writing "" gives an empty string. It displays as nothing but is text, so ISBLANK is FALSE, COUNTA counts it, and a chart may plot it as zero rather than as a gap. Use NA() as the fallback if a chart should break the line.
Errors IFERROR returns
| Error | What it means and how to fix it |
|---|---|
| #VALUE! | Produced by IFERROR itself only when the wrong number of arguments is supplied - it takes exactly two. A #VALUE! reaching the cell otherwise came from the fallback, since anything from the first argument would have been caught. |
| #NAME? | The function is misspelled, or a text fallback was left unquoted. It can also mean the workbook is open in Excel 2003, where IFERROR does not exist. |
| #N/A | Coming from value_if_error, not from value. A fallback of NA() is a deliberate way to keep a chart from plotting a missing point, so this is sometimes intended. |
| #SPILL! | The first argument returned an array and the cells it needs are occupied. IFERROR preserves the shape of what it is given, so wrapping an array formula does not make it fit into one cell. |
| A wrong number displayed with no error | The most damaging outcome. A #REF! or #NUM! that signalled a real defect was replaced with 0 or "", so the total below it is quietly wrong. Remove the IFERROR temporarily whenever a figure looks off. |
More examples
=IFERROR(VALUE($A2), 0)Returns: The number in A2, or 0 when it is not numeric
Cleaning an imported column where some cells hold text. A 0 fallback keeps the column numeric so the SUM below it still works.
=IFERROR(INDEX($D$2:$D$500, MATCH($A2, $C$2:$C$500, 0)), NA())Returns: The matched value, or a real #N/A
Deliberately falling back to NA() rather than "" so that charts break the series and COUNT ignores the row, instead of treating a missing value as zero.
=IFERROR($B2/$C2, IFERROR($B2/$D2, ""))Returns: A ratio from the first available denominator
Nested IFERROR calls try a second denominator before giving up. Two levels is about the limit before a helper column reads better.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Available from Excel 2007. IFNA arrived in 2013, so on 2007 and 2010 the choice is IFERROR or the older IF(ISERROR(...)) pattern. |
| Excel 2019 | Supported, alongside IFNA and IFS. Still the only single-call way to catch every error type. |
| Excel 2021 / Microsoft 365 | Unchanged, but XLOOKUP's if_not_found argument replaces most lookup-related uses, and it also catches #SPILL! and #CALC!. |
| Google Sheets | Same syntax, and the second argument may be omitted, in which case Sheets returns a blank - Excel requires both arguments. IFNA is available too. |
| LibreOffice Calc | Supported since version 4.0. Older files using IF(ISERROR(...)) convert cleanly but are not rewritten automatically. |
Does IFERROR Work in Google Sheets?
Yes. IFERROR uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements IFERROR with exactly two arguments, and returns the fallback for any of the error values it recognises, including #SPILL! and #CALC!.
The test reduces the first argument to a single value before checking it, so a multi-cell range passed as value is treated as an error and the fallback is returned. The editor does not test an array element by element the way Excel does.
When the first argument is not an error it is returned unchanged, keeping its original shape, so an array result still spills. IFNA is implemented as well, replacing only #N/A and leaving every other error visible.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
IFERROR questions
Whenever the only failure you expect is a lookup that found nothing. IFNA replaces #N/A and lets everything else through, so a #REF! from a deleted column or a #VALUE! from a bad argument still shows up as a problem to fix rather than as your friendly fallback text. IFERROR is the right choice only when several different errors genuinely mean the same thing to the reader. IFNA needs Excel 2013 or later.
No, the opposite if anything. The first argument is evaluated in full before IFERROR can know whether it errored, and the fallback is evaluated too. A formula like =IFERROR(VLOOKUP(A2, big, 3, FALSE), VLOOKUP(A2, other, 3, FALSE)) performs both lookups on every row. If speed matters, compute the lookup once in a helper column and test that.
0 if the column is summed, because a text fallback such as "-" or "n/a" makes SUM ignore that row but breaks anything that expects numbers. "" if a person reads the column and nothing should be summed from it. NA() if the value is genuinely unknown and you would rather AVERAGE and charts skip it than treat it as zero - averaging a column where missing values became 0 is one of the quieter ways a report goes wrong.
It is a risk worth naming rather than a rule. Wrapping the outermost call means every error anywhere inside it is suppressed, including ones that indicate the formula itself is wrong - a mistyped range, a deleted column, an argument in the wrong position. The narrower habit is to wrap only the part that can legitimately fail, and to use the most specific catcher available: XLOOKUP's if_not_found, then IFNA, then IFERROR.