CHOOSEROWS

What it does

CHOOSEROWS(array, row_num1, [row_num2], ...)

CHOOSEROWS(array, row_num1, [row_num2], …) builds a new array from the rows of array named by the index arguments, in the order the indices are given. Column count is invariant; only the row axis is touched.

Because the output is driven by the index list rather than by a range, the function does three jobs at once:

1. selection — keep the rows you name, drop the rest;
2. reordering — CHOOSEROWS(A, 3, 1, 2) permutes;
3. repetition — an index may appear more than once, and the output row count is the number of indices, not the number of distinct indices.

Indices are one-based. Microsoft documents that a negative index counts from the end: -1 is the last row. There is no zero index.

The everyday uses follow directly: CHOOSEROWS(A, 1) takes a header row, CHOOSEROWS(A, -1) takes the last record, and CHOOSEROWS(A, SEQUENCE(…)) takes a computed sample — the last of which depends on how the function treats an array-valued index argument, which is the open question flagged below.

Arguments

The argument position most often misjudged is the index list's ordering semantics: readers reach for CHOOSEROWS expecting a filter (which preserves original order) and get a permutation (which does not). If you want "the rows where a condition holds, in their original order", FILTER is the function; CHOOSEROWS gives you exactly the order you asked for, including an order you did not mean to ask for.

Try it

Quirks

Result and edge cases

Return kind: Array, of shape (number of index values) × (columns of array). It spills; spilling itself is host-side adaptation, described in the call pipeline.

• References are resolved first. array is prepared under ValuesOnlyPreAdapter, so the result is values, not a reference into the original range. Nothing downstream can treat the result as a range.
• No coercion of selected elements. Text stays text, logicals stay logicals.
• A single-row array admits only index 1 (or -1); everything else is out of range.

Errors

Microsoft's page documents the failure directly: CHOOSEROWS returns #VALUE! if any row_num argument is zero or exceeds the number of rows in array; the same bound applies on the negative side by the symmetry of the documented indexing.

An error value supplied as an argument — as opposed to sitting inside array — propagates under the universal coercion rule in coercion and lifting.

Whether a fractional index truncates or errors is not established here.

How to implement

Notes for implementers

• Do not normalize the index list. Sorting or deduplicating it changes the result. The common case (distinct ascending indices) hides the bug completely.
• Three index branches, not two: positive, negative, and zero-is-an-error. Negative indices must be resolved against the input height before the bounds check.
• Row selection is a gather, not a slice. Implementations that assume contiguity — because the common calls are contiguous — will pass their tests and fail on CHOOSEROWS(A, 3, 1).
• The module is shared with the rest of the dynamic-array reshapers (dynamic_array_reshape_family.rs), so bounds-and-shape logic is common code; a fix here is a fix for the siblings, and a regression here is a regression for them.

Rust code

8 chooserows_fn "CHOOSEROWS" (2, 255) [R "array", R "row_num1", R "row_num2"] |s, a, sv| {
    choose(s, a, sv, k::eval_chooserows_prepared)
}

pub fn eval_chooserows_prepared(
    args: &[CalcValue],
) -> Result<CalcValue, DynamicArrayReshapeEvalError> {
    let array = materialize_array_arg(&args[0]);
    let rows: Vec<usize> = args[1..]
        .iter()
        .map(parse_integer)
        .map(|result| result.and_then(|idx| resolve_selector(idx, array.shape().rows)))
        .collect::<Result<_, _>>()?;

    let mut cells = Vec::with_capacity(rows.len() * array.shape().cols);
    for row in &rows {
        cells.extend(
            array
                .row_slice(*row)
                .expect("validated selector")
                .iter()
                .cloned(),
        );
    }
    build_array(rows.len(), array.shape().cols, cells)
}

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

• CHOOSECOLS is the identical operation on the column axis. Anything established for one should be probed for the other rather than assumed, but the pair is symmetric by construction and shares an implementation module.
• TAKE / DROP handle contiguous edge selections — "first 5 rows", "all but the last 2" — without enumerating indices, and express intent better when the selection is contiguous.
• FILTER selects rows by predicate and preserves their original order. Use it when the criterion is a condition; use CHOOSEROWS when the criterion is a position.
• SORT / SORTBY also produce a permutation of rows, but derive it from the data rather than from an explicit index list. SORTBY with a computed key and CHOOSEROWS with a computed index list are two routes to the same reordering.
• INDEX with column_num = 0 returns an entire row and is the pre-dynamic-array idiom CHOOSEROWS supersedes for multi-row selection.
• VSTACK is the joining counterpart: CHOOSEROWS takes rows out, VSTACK puts rows together.
• CHOOSE shares only a prefix. It selects among arguments, not among rows.

Named variants

No named variants are recorded yet.