Implicit intersection (@)

What it does

@A

@A reduces its operand to a single value, using the position of the calling cell to decide which one.

1. Already a single item — returned unchanged.
2. A reference or range — scalarized relative to the caller: the same-row or same-column cell of that range, chosen against the formula's anchor. A two-dimensional range yields #VALUE! on the admitted native baseline.
3. An array payload rather than a reference identity — scalarized to the top-left item.
4. A spill-capable expression — removing @ changes the publication surface from scalarization to spilling, subject to host spill rules.

Cases 2 and 3 differ in which item survives, and the difference is the reason the operator needs references visible: a range is scalarized positionally, an array is scalarized to its first element. An implementation that dereferences the operand before @ sees it cannot tell the two apart.

Why the character exists at all

Before dynamic arrays, a formula that handed a range to a scalar-shaped argument got this scalarization silently — "implicit intersection" is the historical name for the silent behaviour. When Excel gained dynamic arrays, the same expression started spilling instead. @ is the explicit marker that preserves the old meaning: it makes the intersection visible in the formula text so that legacy workbooks keep computing what they always computed. The contract puts it plainly: explicit @ "is a modern surface marker for implicit-intersection behavior that older Excel performed silently".

That is also why @ appears unbidden in formulas that were written years earlier: opening an old workbook in a dynamic-array build can surface the marker that was previously implicit. The character is a compatibility artifact, not a new feature.

One operator, several spellings

Chapter 05 records SINGLE(...) and _xlfn.SINGLE(...) as compatibility and serialization representations of this same operator identity — not separate semantics. The contract adds that the host baseline normalizes _xlfn.SINGLE(...) back onto explicit @ in the modern formula property. So a reader may encounter three spellings of one function.

Arguments

Arity is exactly 1; no optional arguments, no defaults. The operator is prefix.

Try it

Quirks

Result and edge cases

Returns whatever single value the scalarization selects — a scalar of any kind, or an error. The kernel class is Custom; there is no meaningful signature in the numeric sense.

• Scalar operand. Passthrough.
• One-row or one-column range. The cell in the caller's column or row respectively.
• A range that does not intersect the caller's row or column. The historical behaviour of silent implicit intersection was #VALUE! in this case; the contract's admitted lane records #VALUE! for the two-dimensional case, and this page does not extend that to every non-intersecting shape without evidence.
• Two-dimensional range. #VALUE! on the admitted native baseline.
• Array payload. Top-left element.
• Empty array. Chapter 01 lists #CALC! as the error for an engine that cannot produce a value, including the empty-array case. Whether @ on an empty array reaches it is not recorded here.
• Inside a lambda or a function argument. The "calling cell" for a nested evaluation is an execution-context question (chapter 04), not an operator question, and this page does not settle it.

Note one structural consequence: because the operator needs a workbook and a calling cell, it cannot be exercised by a value-only dispatch harness at all. The Handbook's mechanical battery records it as not dispatchable for exactly that reason — a fact about the harness, not about the operator.

Errors

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

How to implement

Notes for implementers

• The presence of @ must survive parsing and binding into evaluation metadata even when the stored form omits the token. The contract states this as a hard requirement, and it is the single thing most likely to be got wrong: an evaluator that reconstructs @ from the stored formula text alone will lose it.
• Keep reference identity distinct from array payload all the way to the operator. The two cases scalarize differently.
• The calling cell's position is an input. Any caching keyed only on the operand is wrong.
• Treat the three spellings as one identity with a normalization step, not as three functions.
• This operator is HostSerialized: it is not safe to evaluate on an arbitrary worker thread the way the arithmetic operators are.

Rust code

/// What implicit intersection takes of `value` for a formula at `at`: a reference to one cell, or
/// a value. An array's empty first element is the empty value.
pub(super) fn implicit(value: &CalcValue, at: CellKey) -> CalcValue {
    match value.core_ref() {
        CoreRef::Reference(reference) => match intersect_cell(reference, at) {
            Some(cell) => CalcValue::reference(ReferenceLike::cell(cell)),
            None => CalcValue::error(WorksheetErrorCode::Value),
        },
        CoreRef::Array(array) => array
            .get(0, 0)
            .cloned()
            .expect("an array holds at least one element"),
        _ => value.clone(),
    }
}

/// The cell implicit intersection picks of `reference` for a formula at `at`, if any.
pub(super) fn intersect_cell(reference: &ReferenceLike, at: CellKey) -> Option<CellKey> {
    let [area] = reference.areas() else {
        return None;
    };
    let (top, left, bottom, right) = bounds(*area);
    let row = if top == bottom {
        top
    } else if (top..=bottom).contains(&at.row()) {
        at.row()
    } else {
        return None;
    };
    let column = if left == right {
        left
    } else if (left..=right).contains(&at.column()) {
        at.column()
    } else {
        return None;
    };
    CellKey::new(area.sheet(), row, column).ok()
}

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_SPILL_REF — the other dynamic-array operator, and the opposite motion: # names a whole spilled region, @ refuses to spill.
• FUNC.OP_INTERSECTION_REF — the explicit intersection of two references, spelled with a space. Despite the shared word, it is a different operation with different operands; confusing the two is common and this page names the confusion deliberately.
• SINGLE, _xlfn.SINGLE — the same identity under compatibility and serialization spellings (chapter 05).
• INDEX, INDIRECT, OFFSET, XLOOKUP — reference-returning functions whose results @ consumes; the contract treats them as supporting substrate that @ does not redefine.
• TAKE(x,1,1), INDEX(x,1,1) — explicit alternatives when the intent is "the first element" rather than "the historically intersected element". They mean what they say; @ means something position-dependent.

Named variants

No named variants are recorded yet.