SWITCH

What it does

SWITCH(expression, value1, result1, ...)

SWITCH evaluates one expression once and compares it, in order, against a list of candidate values, returning the result paired with the first candidate that matches. If nothing matches, it returns an optional trailing default — and if there is no default, #N/A.

The rule:

1. Evaluate expression once.
2. Walk the (valueN, resultN) pairs left to right. For each, test expression = valueN under the function's equality predicate.
3. On the first match, return resultN and stop.
4. If no candidate matched, return default if one was supplied, otherwise #N/A.

Two things make SWITCH more interesting than "IFS with an implicit =".

Parity decides the default. There is no keyword marking the default argument. SWITCH counts: the expression takes one slot, and candidate/result pairs take two each, so an even total argument count means the last argument is unpaired and is therefore the default. An odd total means every candidate has a result and there is no default. Nothing in the formula text says "default" — the argument count says it. Miscount the pairs and a value you meant as a candidate becomes the default, silently.

If you take one thing from this page: SWITCH is a comparison function wearing dispatch clothing, and its comparison is the same tolerant-looking one the = operator and COUNTIF use, not the exact one lookup functions use.

Arguments

SWITCH(expression, value1, result1, [value2, result2], ..., [default])

• expression — required. Evaluated once. Any value kind.
• default — optional, and identified only by argument parity as described above.

The commonly misunderstood positions are the last two: readers assume a named default and assume that a numeric candidate matches a numeric-looking text expression. It does not — the reference engine matches text against text and numbers against numbers, and does not cross the kind boundary.

Try it

Quirks

Result and edge cases

Returns whatever kind the selected result carries.

• No match, no default. #N/A.
• No match, default present. The default, verbatim.
• Numeric matching. Through the 15-significant-digit truncation predicate described above, not raw IEEE equality.
• Kind mismatch. A number and numeric text do not match each other.
• Error as the expression. Propagates.
• Selected result is an error. Returned verbatim; SWITCH selects, it does not inspect.
• Arrays. The call model records SWITCH under ByIndexScalarArrayLift over positions 0, 1 and 3 — the expression, the first candidate, and the first result (chapter 03). As with IFS, that list is irreducible structure rather than a derivable rule. Once a scalar comparison has selected a result, later arrays do not lift the call: SWITCH(2,2,1,{1,2},3) returns one 1 (FN-IFS-001).

How to implement

Notes for implementers

1. Compute the default by parity before scanning, and treat "is there a default" as a structural fact of the call, not something discovered mid-loop.
2. Do not evaluate candidates or results past the first match. Lazy evaluation is observable whenever a later argument would error.
3. Choose the equality predicate deliberately and document it. If you share a comparison helper with =, COUNTIF, and SUMIF, SWITCH inherits their truncation semantics. If you share one with MATCH, it inherits raw IEEE equality. Both are defensible; silently inheriting whichever helper was nearest is not.

Rust code

CatalogueEntry {
    id: SWITCH,
    spelling: "SWITCH",
    notation: Notation::Call,
    arity: CallableArityShape::range(3, 254),
    parameters: &[
        Parameter::value("expression"),
        Parameter::value("value1"),
        Parameter::value("result1"),
    ],
    demand: ArgumentDemand::Eager,
    kernel: Kernel::Eager(switch),
}

fn switch(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    selection(SWITCH, arguments, services, |s, a| {
        match switch_index(s, a)? {
            Some(index) => Ok(selected(&a[index])),
            None => fail(WorksheetErrorCode::NA),
        }
    })
}

/// The selected SWITCH argument, or no match without a default. Both shape admission
/// and the kernel use this one selector; comparisons read no host or random services.
fn switch_index(site: &Site<'_>, arguments: &[CalcValue]) -> Step<Option<usize>> {
    if let CoreRef::Error(code) = arguments[0].core_ref() {
        return fail(code);
    }
    let has_default = arguments.len().is_multiple_of(2);
    let cases = arguments.len() - usize::from(has_default);
    let mut at = 1;
    while at + 1 < cases {
        let pair = [arguments[0].clone(), arguments[at].clone()];
        if order(site, &pair, Want::Equality)? == Ordering::Equal {
            return Ok(Some(at + 1));
        }
        at += 2;
    }
    Ok(has_default.then_some(arguments.len() - 1))
}

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

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• IFS is the sibling for independent conditions. Use IFS when the tests differ in kind; use SWITCH when they are all equality against one expression. Both return #N/A on no match, but only SWITCH has a real default slot.
• IF nested is what both replace.
• CHOOSE dispatches on a 1-based index rather than on a value match.
• LOOKUP / XLOOKUP / MATCH are the table-driven alternatives, and they are on the other side of the equality split recorded in EV-STRUCT-0001: MATCH and XMATCH compare with raw IEEE equality. Replacing a SWITCH with an XLOOKUP over a two-column table therefore changes the comparison semantics, not just the notation. That is a genuinely surprising migration hazard and the most practical consequence of this page.
• SWITCH arrived in Excel 2016. It supersedes nothing and is not a Compatibility-category entry.

Named variants

No named variants are recorded yet.