IF

What it does

IF(logical_test, value_if_true, [value_if_false])

IF selects between two values according to the truthiness of a condition, and returns the selected value verbatim — it does not coerce, reshape, or inspect what it returns.

The rule:

1. Prepare logical_test and reduce it to a truth value. A Logical is itself; a Number is false when exactly 0 and true otherwise; an Empty or omitted condition reads as false; text that does not read as a number fails; an error propagates.
2. If the condition is true, return value_if_true. Otherwise return value_if_false.
3. Only the selected branch's value is delivered. IF is classified as a lazy branch selector in the call model: the unselected branch does not contribute to the result, which is why IF(TRUE, lambda) can pass a callable value through unchanged (chapter 03, "Functions that step outside the plain pipeline").

Two structural properties follow from "verbatim", and they are what make IF more than sugar:

• IF is kind-preserving. If the selected branch is text, IF returns text; if it is an error, IF returns that error. IF is not an error handler — IFERROR is.

Arguments

IF(logical_test, value_if_true, [value_if_false])

• logical_test — required. A condition, or anything that can be reduced to one. The most common misunderstanding lives here: logical_test is not required to be a Logical. Numbers work, and the zero test is exact.

There is a third case that is neither "supplied" nor "omitted": a present but empty slot, as in IF(cond, 1, ). Microsoft's page addresses this obliquely under common problems, noting that a missing argument for either branch shows as 0 in the cell. So the documented reading is that an empty slot yields 0 while a genuinely absent third argument is undocumented — and the reference engine distinguishes Missing from Empty at exactly this seam (chapter 01, "Missing versus Empty"). Whether Excel's two cases really differ is listed below as unchecked, and it is the most useful probe on the page.

Try it

Quirks

Result and edge cases

Returns whatever kind the selected branch carries: Number, Text, Logical, Error, or an array.

• Condition is an empty cell. Reads as false; the false branch is taken.
• Condition is an error. Propagates; neither branch is selected.
• Branch is an error. Returned as-is. IF(TRUE, 1/0, 0) is #DIV/0!.

Errors

How to implement

Notes for implementers

1. Laziness is observable. Evaluating both branches and then discarding one changes behaviour whenever a branch is expensive, volatile, or returns a callable. The call model classifies IF as a lazy branch selector for that reason; an eager implementation is not merely slower.
2. Reference awareness is not optional. Because IF sees live references, the branch it does not take is never dereferenced. An implementation that resolves arguments before dispatch will produce reference-resolution errors that Excel would never raise.
3. Shape first, then select. With an array condition, the result shape is the condition's shape; branches are materialised against it. Deciding shape after selection gives the wrong answer whenever one branch is scalar and the other is not.

Rust code

CatalogueEntry {
    id: IF,
    spelling: "IF",
    notation: Notation::Call,
    arity: CallableArityShape::range(2, 3),
    parameters: &[
        Parameter::value("logical_test"),
        Parameter::value_or_reference("value_if_true"),
        Parameter::value_or_reference("value_if_false"),
    ],
    demand: ArgumentDemand::BranchOnCondition,
    kernel: Kernel::Lazy(if_selected),
}

/// IF: the condition alone is asked for first and read as a logical; its error is the result.
/// Then only the chosen branch is asked for, and it is the result as it is (a reference stays a
/// reference); an omitted branch is 0, and a FALSE condition without a third argument is FALSE.
fn if_selected(
    arguments: &mut LazyArguments<'_>,
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(IF, services);
    let condition = arguments.argument(0)?;
    let branch = match coerce::logical(&condition) {
        Ok(true) => 1,
        Ok(false) if arguments.count() > 2 => 2,
        Ok(false) => return Ok(CalcValue::logical(false)),
        Err(stop) => return Site::finish(Err(site.stop(stop, Some(0)))),
    };
    let chosen = arguments.argument(branch)?;
    Ok(if chosen.is_missing() {
        CalcValue::number(0.0)
    } else {
        chosen
    })
}

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

• IFS replaces long IF(a, x, IF(b, y, IF(c, z, ...))) chains with a flat condition/value list. It is not a superseding pair — Microsoft retains both and they are not equivalent at the edges: IFS returns #N/A when nothing matches, where a nested IF chain returns its innermost false branch.
• SWITCH compares one expression against a list of candidate values rather than evaluating independent conditions.
• IFERROR and IFNA branch on error-ness rather than on truth, and they are what people reach for after discovering that IF returns errors verbatim.
• CHOOSE selects by 1-based index rather than by condition.
• AND / OR are the usual condition builders; note that they evaluate all their arguments, so IF(AND(...), ...) does not short-circuit the way a programmer expects.
• Readers confuse IF with IFS most often, and with SUMIF/COUNTIF second most often — the latter are aggregates whose IF is a criteria filter, not a branch.

Named variants

No named variants are recorded yet.