Intersection of references (space)

What it does

A B

A B — two references separated by a space — returns the reference designating the cells belonging to both.

When the operands do not overlap, the result is #NULL!. The contract records this directly, and chapter 01's error table defines #NULL! as exactly that: the intersection of ranges that do not intersect, produced by the space operator. This is the only place in the entire function surface where #NULL! originates. If a workbook shows #NULL!, an intersection somewhere came up empty — very often an accidental one, because the operator is spelled with a character that is otherwise invisible.

That invisibility is the operator's defining hazard. =SUM(A1:A5 B1:B5) looks like a typo for a comma and evaluates as an intersection; =A1 A2 is a valid expression. Whitespace inside a formula is not always insignificant, and this is why.

The classic intentional use is with defined names: if Revenue names a column and Q2 names a row, then =Revenue Q2 reads the cell where they cross. That idiom is the reason the operator exists and is worth knowing even if you never write another one.

Arguments

Arity is exactly 2; no optional arguments, no defaults. Intersection is commutative and associative on areas, so chains behave as expected. Both operands must be reference-shaped; the contract records the admitted operand shapes as A1-style cells, areas, whole rows and whole columns.

Try it

Quirks

Result and edge cases

• No overlap. #NULL!.
• Partial overlap. The overlapping rectangle, which may be a single cell, a row segment, a column segment, or a block.
• Whole-row against whole-column. 1:1 A:A is the single cell at their crossing — the mechanism behind the defined-name idiom above.
• Multi-area operands. Intersecting a multi-area reference (from FUNC.OP_UNION_REF) with an area is not in the contract's admitted slice, and the result shape is not settled here.
• Non-reference operands. No intersection exists; the result is an error.
• Cross-sheet operands. Not covered by the admitted slice.

Errors

How to implement

Notes for implementers

• The empty check must come before any dereference. Producing an empty area and letting a consumer fail on it gives the wrong error code.
• Whitespace handling is a parser responsibility, but the operator identity must not be lost in it: a formula's stored form records the spacing, which is exactly why the trim-reference operators exist as separate identities.
• Sheet-qualified and multi-area operands are outside the contract's admitted slice; an implementation should state what it does rather than assume.

Rust code

/// `left right`.
pub(super) fn intersect(left: &CalcValue, right: &CalcValue) -> CalcValue {
    let operands = [left.clone(), right.clone()];
    let references = match references(&operands) {
        Ok(references) => references,
        Err(value) => return value,
    };
    let mut out = Vec::new();
    for a in references[0].areas() {
        for b in references[1].areas() {
            let (at, al, ab, ar) = bounds(*a);
            let (bt, bl, bb, br) = bounds(*b);
            let (top, left, bottom, right) = (at.max(bt), al.max(bl), ab.min(bb), ar.min(br));
            if top <= bottom && left <= right {
                out.push(area(*a, top, left, bottom, right));
            }
        }
    }
    match ReferenceLike::from_areas(out) {
        Ok(reference) => CalcValue::reference(reference),
        Err(_) => CalcValue::error(WorksheetErrorCode::Null),
    }
}

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 join; this operator is the meet.
• FUNC.OP_UNION_REF — the list constructor.
• FUNC.OP_IMPLICIT_INTERSECTION — a different operation with a confusingly similar name. @ scalarizes one operand relative to the calling cell; this operator intersects two references with each other. They are not variants of one idea.
• FUNC.OP_TRIM_REF_LEADING and its siblings — the whitespace-normalization operators, which exist because whitespace around references is meaningful enough to need identities of its own.
• INDEX, OFFSET, XLOOKUP — the modern, explicit ways to name a cell at the crossing of two coordinates, and what most workbooks should use instead.
• IFERROR — because #NULL! is an error like any other and can be trapped.

Named variants

No named variants are recorded yet.