IFERROR

What it does

IFERROR(value, value_if_error)

IFERROR(value, value_if_error) returns value unless value is an error value of any code, in which case it returns value_if_error.

The test is on the kind of the value, not on its content: the function asks "is this an Error?" and nothing else. All fourteen worksheet error codes (chapter 01) are masked equally — #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, #NULL!, and the extended family alike.

This is the crucial property and the crucial danger. IFERROR is a total mask: it cannot distinguish a divide-by-zero you expected from a #REF! caused by a deleted column or a #NAME? caused by a typo in the very formula it wraps. Wrapping a lookup in IFERROR to suppress "not found" also suppresses every structural break in that lookup, silently and permanently. IFNA exists precisely because that trade is usually a bad one.

IFERROR is one of the declared exceptions to the engine's universal error-propagation rule: the coercion model states that errors propagate through conversion unless a family explicitly declares branching behaviour, and IFERROR is named as such a declaration (chapter 02, "Error propagation").

Like IF, it is a lazy selector — the fallback is only reached when the primary is an error.

Arguments

IFERROR(value, value_if_error)

• value — required. The expression to test. In practice this is the whole formula you wanted to write; IFERROR wraps it.

Try it

Quirks

Result and edge cases

Returns whatever kind the selected argument carries.

• Primary is not an error. Returned verbatim, including text, logicals, and arrays.
• Primary is any error. The fallback is returned, verbatim.
• Fallback is itself an error. Returned as-is. IFERROR(1/0, NA()) is #N/A — the mask does not iterate.
• Empty text as the primary. IFERROR("", "") is the empty string. Empty text is a perfectly good non-error value.
• Arrays. The function's own classification is surface-native; an array primary containing a mix of errors and values raises the question of whether the mask applies elementwise or to the array as a whole. That question is listed below as unchecked, and it is the most consequential unknown on this page for modern dynamic-array formulas.

Errors

How to implement

Notes for implementers

1. Test the kind, not the code. Any per-code logic belongs in IFNA, not here. If your IFERROR needs a list of error codes, the design has already gone wrong.
2. Do not evaluate the fallback eagerly. The whole point of IFERROR over the ISERROR idiom is single evaluation of the primary and no evaluation of the fallback when it is not needed.
3. Decide the array policy explicitly and write it down. Elementwise masking and whole-array masking give different answers for any array containing both errors and values, and this is exactly where a dynamic-array-era implementation will diverge from a legacy one.

Rust code

CatalogueEntry {
    id: IFERROR,
    spelling: "IFERROR",
    notation: Notation::Call,
    arity: CallableArityShape::exact(2),
    parameters: &[
        Parameter::value("value"),
        Parameter::value("value_if_error"),
    ],
    demand: ArgumentDemand::FallbackOnError,
    kernel: Kernel::Lazy(iferror_selected),
}

/// IFERROR catches every worksheet error. Microsoft's page lists seven (#N/A, #VALUE!, #REF!,
/// #DIV/0!, #NUM!, #NAME? and #NULL!); Excel 16.0.20430.20182 catches #GETTING_DATA, #CALC!,
/// #SPILL! and #FIELD! too (bead dc-ibp2's capture). It catches #BLOCKED!, #CONNECT! and #BUSY!
/// as well, which no capture measures: the functions that give them in Excel are external-data
/// routes the capture refuses.
fn iferror_selected(
    arguments: &mut LazyArguments<'_>,
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    fallback_selected(IFERROR, arguments, services, |_| true)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• IFNA is the surgical version: it masks #N/A only and lets every other error through. For lookup formulas this is almost always the right choice, and the pair IFERROR/IFNA is the clearest "total versus selective" contrast in the function library.
• ISERROR, ISERR, ISNA are the inspection functions; IF(ISERROR(x), y, x) is the pre-2007 idiom IFERROR replaced, and it evaluates x twice. ISERR is ISERROR minus #N/A — the same distinction IFNA draws, from the other side.
• AGGREGATE with the appropriate option skips errors inside an aggregation, which is the aggregate-shaped analogue of masking.
• IF returns branch errors verbatim and is the function people wrongly expect to handle errors.
• IFERROR arrived in Excel 2007. Workbooks that must open in Excel 2003 use the double-evaluation IF(ISERROR(...)) idiom instead; that is a version-compatibility fact, not a Compatibility-category supersession, and Microsoft has not deprecated anything here.

Named variants

No named variants are recorded yet.