Union of references (,)

What it does

A, B

A , B takes two references and returns a reference designating both areas — a multi-area reference, not a merged rectangle.

Two consequences that a reader can observe:

• Areas keep their identity. The contract records INDEX((A1:A2,B1:B2),2,1,2) selecting from the second area — INDEX's fourth argument is an area number, which only means something because the union preserved the areas separately. Any function taking an area index (INDEX, AREAS) is consuming exactly this structure.
• Overlaps are not de-duplicated. A union is a list of areas, not a set of cells. The contract's SUM((A1,B1)) finding is a plain two-cell sum, but a union whose areas overlap presents the overlapping cells more than once to a consumer that scans areas in order. Whether every consumer double-counts is a per-function question, and the Handbook has not recorded it.

The parenthesis requirement is not decoration. Inside a function call, a comma is the argument separator — grammar, not an operator (chapter 03: parse-only delimiters carry no semantics and get no function identity). SUM(A1,B1) is a two-argument call; SUM((A1,B1)) is a one-argument call whose argument is a union. Both happen to total the same cells here, which is exactly why the distinction is so easy to miss, and why it bites when the consuming function counts its arguments (AREAS, INDEX, SUBTOTAL).

Arguments

Arity is exactly 2; no optional arguments, no defaults. A chain (A1,B1,C1) is repeated application of the two-argument operator, and area order is preserved — so the operator is not commutative in the way a set union would be, because area order is observable through area-indexed consumers.

Both operands must be reference-shaped.

Try it

Quirks

Result and edge cases

• Materialization. When a consumer needs values rather than a reference, the areas must be combined. The contract records same-sheet rect-style multi-area materialization as happening through resolver-driven row-vector combination in member order — that is, the member order of the union decides the resulting value order.
• Cross-sheet unions. The contract's admitted slice is same-sheet. Unions across sheets are not covered by it, and are not settled here.
• Non-reference operands. No union exists; the result is an error.
• Overlapping areas. Preserved as written; see above.

Errors

data/functions/FUNC.OP_UNION_REF.json records no Microsoft documentation URL (docs is null).

How to implement

Notes for implementers

• Represent a union as a first-class ordered list of areas, from the moment it is formed. Flattening it to a bounding box, or to a string, loses the identity that AREAS and INDEX depend on — which is precisely the seam BUG-FUNC-003 describes.
• Preserve member order, and materialize in member order.
• Do not de-duplicate overlaps at formation time. Whether a consumer double-counts is the consumer's semantics, and collapsing early removes the consumer's ability to decide.
• Keep the operator distinct from the argument separator at every layer. If the same token handling produces both, a parenthesized union and a two-argument call will eventually be confused, and the failure will look like an arity bug rather than a reference bug.

Rust code

/// `(a,b,...)`.
pub(super) fn union(operands: &[CalcValue]) -> CalcValue {
    let references = match references(operands) {
        Ok(references) => references,
        Err(value) => return value,
    };
    let areas = references
        .iter()
        .flat_map(|reference| reference.areas().iter().copied())
        .collect();
    match ReferenceLike::from_areas(areas) {
        Ok(reference) => CalcValue::reference(reference),
        Err(_) => CalcValue::error(WorksheetErrorCode::Value),
    }
}

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

• FUNC.OP_RANGE_REF — the bounding-box constructor; the join to this operator's list.
• FUNC.OP_INTERSECTION_REF — the meet.
• AREAS — the function that counts the members of a multi-area reference. It exists essentially to inspect the output of this operator.
• INDEX with a fourth argument — area selection within a multi-area reference.
• SUM, COUNT and the other aggregates — the usual consumers, and the ones whose double-counting behaviour on overlapping unions is worth checking.
• The locale-dependent list separator is a grammar question. In locales where function arguments are separated by ;, whether the union operator is still spelled , is not recorded in this Handbook's sources, and this page does not assert it.

Named variants

No named variants are recorded yet.