IFS

What it does

IFS(test1, value1, [test2, value2], …)

IFS(test1, value1, [test2, value2], …) chooses the value paired with the first true test. Order matters: put more specific or higher-priority conditions first. Microsoft documents up to 127 condition/value pairs.

There is no separate default argument. A final TRUE, fallback pair supplies a default; without any true test, the result is #N/A.

For example, =IFS(85>=90,"A",85>=80,"B",TRUE,"C") selects "B".

Try it

Start with the example's first true condition, then make it false to reach the fallback. Remove the final TRUE pair to see the unmatched case. Keep the condition/value pairs balanced.

Quirks

Microsoft documents #VALUE! if an evaluated condition cannot resolve to TRUE or FALSE, and an entry-time error message if a condition has no paired value. Those are distinct from an unmatched but otherwise valid call returning #N/A.

The selected value can retain its reference identity. On the recorded build, ISBLANK(IFS(TRUE,Data!Z1)) returns TRUE when that cell is empty. Converting every selected reference to a number would lose this behaviour.

Later arrays do not necessarily enlarge the result. The recorded IFS(TRUE,1,{FALSE,TRUE},{2,3}) returns one 1. This observation is about the selected result and shape; it does not establish that every unselected expression avoids evaluation. Do not infer a general short-circuit guarantee from the phrase “first true condition”.

How to implement

Represent the condition/value pairs explicitly and reject unpaired syntax at the formula boundary. Determine the first matching condition according to Excel's logical coercion and error rules, then return its corresponding value with its reference or callable identity intact.

Treat argument evaluation policy and result selection as separate design choices. Test an unselected error, volatile function, recursive lambda and array independently before replacing IFS with a chain of lazy IF operations. Selecting the same visible value does not prove the same evaluation behaviour.

For array inputs, measure which conditions establish the output shape and when selection stops that process. Apply errors per output coordinate where the measured rule calls for it. A blanket broadcast over every argument contradicts the scalar-selection example above.

Rust code

CatalogueEntry {
    id: IFS,
    spelling: "IFS",
    notation: Notation::Call,
    arity: CallableArityShape::range(2, 254),
    parameters: &[
        Parameter::value("logical_test1"),
        Parameter::value("value_if_true1"),
    ],
    demand: ArgumentDemand::Eager,
    kernel: Kernel::Eager(ifs),
}

fn ifs(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    selection(IFS, arguments, services, |s, a| {
        if !a.len().is_multiple_of(2) {
            return fail(WorksheetErrorCode::Value);
        }
        match ifs_index(s, a)? {
            Some(index) => Ok(selected(&a[index])),
            None => fail(WorksheetErrorCode::NA),
        }
    })
}

/// The first true IFS condition. Shape admission and selection share logical coercion;
/// the kernel validates pair structure before using the selected result index.
fn ifs_index(site: &Site<'_>, arguments: &[CalcValue]) -> Step<Option<usize>> {
    for pair in (0..arguments.len()).step_by(2) {
        if flag(site, arguments, pair)? {
            return Ok(Some(pair + 1));
        }
    }
    Ok(None)
}

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

Use nested IF when its branch evaluation behaviour is required. Use SWITCH when comparing one expression with several candidate values. Use a lookup table when the conditions encode a growing data table. SUMIFS and COUNTIFS aggregate matching cells; they do not select a single branch.

Named variants

No named variants are recorded yet.