CHOOSECOLS

What it does

CHOOSECOLS(array, col_num1, [col_num2], ...)

Three properties follow from "in the order given" and are the reason the function exists at all:

1. It reorders. CHOOSECOLS(A, 3, 1, 2) is a column permutation.
2. It duplicates. An index may repeat; CHOOSECOLS(A, 1, 1) yields a two-column array whose columns are equal. The output column count is the number of indices, not the number of distinct indices.
3. It projects to fewer or more columns than the input has. Fewer by omission, more by repetition.

Row count is invariant: the result has exactly as many rows as array. Only the column axis is touched.

Indices are one-based, and Microsoft documents that a negative index counts from the end of the array — -1 is the last column, -2 the second to last. There is no zero index; zero is an error, which is the natural consequence of one-based counting from both ends.

Arguments

An index argument may itself be an array — CHOOSECOLS(A, {1,3,5}) — which is how you select a computed set of columns without writing them out. The interaction between an array-valued index argument and several index arguments (CHOOSECOLS(A, {1,2}, 4)) is a shape question the Handbook has not settled.

The position readers misjudge is the first: array is a single argument, so a multi-area reference or a comma-separated list of ranges does not work here the way it does for HSTACK. If you want to combine several sources and then select, stack first, select second.

Try it

Quirks

Result and edge cases

Return kind: Array, of shape (rows of array) × (number of index values). The result spills in dynamic-array Excel; the shared spill and adaptation rules are the host-side concern described in the call pipeline, not function semantics.

• A scalar or 1×1 array. One column exists, so CHOOSECOLS(x, 1) is defined; anything else is out of range.
• A reference argument. array is prepared under ValuesOnlyPreAdapter: the reference is resolved to values before the function runs, so the result is an array of values and carries no reference structure. Consequently CHOOSECOLS cannot be used where a reference is required.
• Mixed types. No coercion is applied to the selected elements; text, logicals and errors pass through as themselves.

Errors

Microsoft's page documents the error condition directly: CHOOSECOLS returns #VALUE! if any col_num argument is zero or exceeds the number of columns in array. By the symmetry of the documented negative indexing, an index more negative than the column count is out of range on the same rule.

Beyond that, an error value supplied as an argument (rather than sitting inside the array) propagates under the universal rule in coercion and lifting.

Whether a fractional index is truncated or rejected is not established here.

How to implement

Notes for implementers

• Output width is the index count, not the distinct-index count. Any implementation that deduplicates or sorts the index list silently changes the answer. This is the easiest bug to write and the hardest to notice, because the common case has distinct ascending indices.
• Negative indices must be resolved against the input width before bounds checking, and the bounds check must reject zero. The three cases — positive, negative, zero — are one branch each; folding zero into either sign's path produces an off-by-one.
• The whole family shares one module in the reference implementation (dynamic_array_reshape_family.rs, shared with CHOOSEROWS, DROP, EXPAND, FILTER, SORT, SORTBY, TAKE, TOCOL, TOROW, TRANSPOSE, UNIQUE, VSTACK, WRAPCOLS, WRAPROWS). Shared code means a shared shape-and-bounds discipline, and it means a test that passes here may be exercising machinery the sibling functions also depend on.
• Empty results are not representable. There is no zero-column array in the value model, so a selection that yields nothing has to become an error rather than an empty result. That is the structural reason the zero index is an error rather than a no-op.

Rust code

9 choosecols_fn "CHOOSECOLS" (2, 255) [R "array", R "col_num1", R "col_num2"] |s, a, sv| {
    choose(s, a, sv, k::eval_choosecols_prepared)
}

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

    let mut cells = Vec::with_capacity(array.shape().rows * cols.len());
    for row in 0..array.shape().rows {
        for col in &cols {
            cells.push(array.get(row, *col).expect("validated selector").clone());
        }
    }
    build_array(array.shape().rows, cols.len(), 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

• CHOOSEROWS is the same function on the other axis, with the same negative-index rule and the same error condition. The pair is symmetric by construction.
• TAKE and DROP select a contiguous block from an edge. CHOOSECOLS selects an arbitrary list. If your selection is "the first three columns", TAKE says it more directly and does not require you to enumerate.
• HSTACK is the inverse operation in spirit: CHOOSECOLS splits columns out of one array, HSTACK joins columns from several.
• FILTER selects along an axis too, but by predicate rather than by index, and it filters rows or columns according to the shape of its include argument.
• CHOOSE shares a prefix and is unrelated: it selects among arguments, not among columns of an array.

Named variants

No named variants are recorded yet.