CHOOSE

What it does

CHOOSE(index_num, value1, [value2], ...)

CHOOSE(index_num, value1, value2, …) is positional selection: it returns the argument at ordinal position index_num in the value list. index_num of 1 selects value1, 2 selects value2, and so on. That is the entire semantic core.

Two properties make it more than a table lookup written sideways.

Arguments

The arity is 2 to 255 — one index plus up to 254 values, which is exactly the ceiling Microsoft's documented index_num range implies.

value arguments may be numbers, text, cell references, defined names, formulas, functions, or — importantly — ranges. The misunderstanding to head off: the values are not a list you index into, they are argument slots. You cannot pass an array of candidates in one argument and have CHOOSE pick from inside it; CHOOSE({10,20,30}, …) indexes the argument list with an array of indices, which is a different operation (see below).

Try it

Quirks

Result and edge cases

The return kind is whatever the selected argument is: a scalar, an array, or a reference. With a scalar index_num there is no coercion of the selected value — that is what "returns it verbatim" means for a branch selector. An array index_num differs, as the first item below says.

Specific behaviours:

• Array index_num. Microsoft documents that if index_num is an array, every value is evaluated when CHOOSE is evaluated. Read that carefully: it is a statement about evaluation, not about the result. The practical consequence is that CHOOSE is not lazy in the array case, so side conditions you were relying on to stay unevaluated (a branch that would error, a slow branch) are evaluated anyway. In dynamic-array Excel CHOOSE then returns an array of selections, and selected empty cells come back as the number 0 rather than as blank references (FN-CHOOSE-001).
• Reference results. Because the selected argument can be a reference, CHOOSE composes with range-consuming functions. Whether the result can serve as an endpoint of the range operator (as INDEX's reference form can) is an open question on this page.
• Omitted argument slots. CHOOSE(2, A1, , B1) delivers the Missing marker into a value slot. Missing and Empty are distinct at the call boundary (value universe); what CHOOSE returns when the selected slot is Missing is a genuine question, since Missing is not admitted at the published-result boundary.
• index_num from text. A direct text argument that reads as a number coerces under the ordinary rules in coercion and lifting; text arriving from a scanned cell is a different lane. CHOOSE is not an aggregate, so the direct/scan asymmetry that governs SUM does not obviously apply — but "does not obviously apply" is not a finding.

Errors

Microsoft's page documents the failure conditions in terms of index_num:

• index_num less than 1, or greater than the number of values supplied, produces #VALUE!.
• index_num that is a fraction is truncated to the lowest integer before being used, so fractions are not by themselves an error.

How to implement

Notes for implementers

• Selection must precede evaluation for the scalar case, and cannot for the array case. Those are two different execution strategies for one function, and the documented array caveat is the seam between them. An implementation that always evaluates all arguments will agree with Excel on results and disagree on cost and on error visibility for branches that throw; an implementation that always defers will disagree with the documented array behaviour.
• The reference must survive selection. Dereferencing a value argument before returning it breaks SUM(CHOOSE(2, A1:A10, B1:B10)) in a way that unit tests on scalars will never catch. This is the practical content of RefsVisibleInAdapter for this function.
• The 254-value ceiling is documented in terms of index_num, and the arity ceiling is 255 arguments. Those are two statements of one limit; an implementation should enforce it in one place.
• The reference implementation places CHOOSE with IFS in a shared module, which is a statement about code organization rather than about Excel: the two functions share a selector skeleton.

Rust code

CatalogueEntry {
    id: CHOOSE,
    spelling: "CHOOSE",
    notation: Notation::Call,
    arity: CallableArityShape::range(2, 255),
    parameters: &[
        Parameter::value("index_num"),
        Parameter::value_or_reference("value1"),
        Parameter::value_or_reference("value2"),
    ],
    demand: ArgumentDemand::BranchOnCondition,
    kernel: Kernel::Lazy(choose),
}

/// CHOOSE(index_num, value1, [value2], ...): index_num alone is asked for first and read as a
/// number, truncated; below 1 or beyond the values it is #VALUE!, and its error is the result.
/// Then only the chosen value is asked for, and it is the result as it is (a reference stays a
/// reference); an omitted value is Empty. An array index_num, for which Excel evaluates every
/// value, is not modelled.
fn choose(
    arguments: &mut LazyArguments<'_>,
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(CHOOSE, services);
    let selector = arguments.argument(0)?;
    if matches!(selector.core_ref(), CoreRef::Array(_)) {
        return Err(FunctionFault::NotImplemented {
            id: CHOOSE,
            argument: Some(0),
        });
    }
    let n = match index_number(&site, 0, &selector) {
        Ok(n) => n.trunc(),
        Err(exit) => return Site::finish(Err(exit)),
    };
    if n < 1.0 || n > (arguments.count() - 1) as f64 {
        return Ok(CalcValue::error(WorksheetErrorCode::Value));
    }
    let chosen = arguments.argument(n as usize)?;
    Ok(if chosen.is_missing() {
        CalcValue::empty()
    } 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

• SWITCH is the modern equivalent for matching a value against a list of cases. Where CHOOSE indexes by position, SWITCH matches by equality and supports a default. New code that is really doing case analysis should use SWITCH.
• IFS — CHOOSE's module sibling in the reference implementation — selects by the first satisfied condition rather than by ordinal.
• INDEX is the near-neighbour that indexes into data rather than into an argument list. If your candidates live in cells, INDEX is the right tool; CHOOSE is for candidates that live in the formula.
• CHOOSECOLS and CHOOSEROWS share a prefix and nothing else. They select columns or rows from a single array; they are dynamic-array reshapers, not branch selectors. The naming collision is the most reliable source of confusion in this corner of the catalog.
• LOOKUP with a constant vector does the same job for larger candidate lists and keeps the candidates out of the formula text.

Named variants

No named variants are recorded yet.