ISREF

What it does

ISREF(value)

ISREF(value) returns TRUE when the argument it receives is a reference — a designator for one or more cell ranges — and FALSE when it is any other kind of value.

Microsoft's condition: "Value refers to a reference."

The kinds it answers TRUE for are the reference shapes listed in the value universe: single cell (A1), area (A1:B10), multi-area union, three-dimensional (Sheet1:Sheet3!A1), structured (Table1[Amount]) and spill anchor (B1#). Whether every one of those shapes actually answers TRUE in Excel is listed below as unmeasured.

Arguments

value — required, exactly one.

The argument is not a values position. It is the only IS-family argument that is not, and that is the entire design of the function. Two consequences follow:

1. What you write in the call matters, not what it evaluates to. ISREF(A1) asks about the expression A1, not about the number sitting in A1. ISREF(1) is FALSE because the literal 1 is not a reference, regardless of what A1 contains.
2. Reference-producing functions can be tested. ISREF(INDIRECT("A1")) and ISREF(OFFSET(A1,1,1)) ask whether those calls produced a usable reference, and that is the idiom the function exists for.

The classic use is validating a sheet name before building a reference from text: ISREF(INDIRECT("'"&name&"'!A1")) is TRUE when the sheet exists and FALSE when it does not, which is how a workbook checks for a sheet without a macro.

Try it

Quirks

Result and edge cases

• ISREF(#REF!) is the interesting case. #REF! is an Error value, not a reference — the reference it once designated is gone. The expected answer is therefore FALSE, which means ISREF is not a "is this reference still valid?" test in the way its name suggests; it is closer to one, because a broken reference degrades to an error rather than staying a reference. This has not been measured here and it is the first thing this page would probe.
• An array literal is FALSE. {1,2;3,4} is an Array, a different kind entirely.
• A defined name that names a range is expected TRUE; a defined name that names a constant or a formula is expected FALSE. Not measured here.
• A spill anchor B1# designates a range and is expected TRUE. Not measured here.
• Numbers, text, logicals, errors and empties are all FALSE.

Errors

Note the asymmetry worth remembering: ISREF returns FALSE where you might expect an error, because "not a reference" is an answer, not a failure. INDIRECT of a bad address returns #REF!; ISREF of that #REF! returns FALSE. The error is produced by the inner function, never by ISREF.

Microsoft's IS-functions page documents no error return for ISREF.

How to implement

Notes for implementers

1. ISREF cannot be implemented on a values-only pipeline. If the dispatch layer resolves references before calling, ISREF will return FALSE for everything, silently. This is the function that proves an engine has a reference-aware argument path at all.
2. Do not dereference inside the function either. ISREF must not touch the cells the reference designates; it inspects the designator. Dereferencing turns a cheap test into a potentially expensive one and can raise errors that ISREF should never produce.
3. Every reference shape needs an explicit decision. Single cell, area, multi-area, 3-D, structured, spill anchor — six shapes, and an engine that models only the first two will quietly disagree with Excel on the rest.
4. No array lift. ISREF is a single-argument inspection, not an elementwise classifier. Routing it through the family's lift helper would change its behaviour on array arguments.

Rust code

unary(ISREF, "ISREF", VALUE_OR_REFERENCE, isref)

/// ISREF: TRUE for a reference, of any number of cells or areas, blank cells included; FALSE for
/// every other value, an array and an error among them. Nothing is read.
fn isref(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(ISREF, services);
    let argument = &arguments[0];
    let step = match argument.core_ref() {
        CoreRef::Reference(_) => Ok(CalcValue::logical(true)),
        CoreRef::Array(_) => Ok(CalcValue::logical(false)),
        _ => scalar(&site, argument).map(|_| CalcValue::logical(false)),
    };
    Site::finish(step)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• INDIRECT is ISREF's usual companion: INDIRECT builds a reference from text at evaluation time (eval_time_deref) and ISREF reports whether the build succeeded.
• OFFSET, INDEX (reference form), CHOOSE over ranges also produce references and can be tested the same way.
• AREAS counts the areas in a reference; between them, ISREF and AREAS are the two functions that treat a reference as an object rather than as a source of values.
• ROW, COLUMN, CELL, SHEET, ISFORMULA, FORMULATEXT are the other reference-aware functions in this Handbook's catalogue; they share ISREF's argument-preparation deviation and none of its semantics.
• ISERROR is what you want if the real question is "did this expression break?" — ISREF answers a narrower and different question.

Named variants

No named variants are recorded yet.