INDIRECT

What it does

INDIRECT(ref_text, [a1])

INDIRECT(ref_text, [a1]) parses a string as a reference and returns that reference. It is the inverse of writing a reference literally: where =A1 names a cell at authoring time, =INDIRECT("A1") names it at evaluation time, from data.

Everything interesting about this function follows from when the reference comes into existence. A literal reference is part of the formula's structure: the engine can see it before evaluation, adjust it when rows are inserted, and use it to order the calculation chain. A reference built by INDIRECT exists only while the formula is being evaluated, and only as the value of an expression the engine cannot inspect in advance.

The text is resolved against the calling context: a bare "A1" means A1 on the calling formula's sheet.

Arguments

ref_text is where every subtlety lives:

• It is a reference expression in text, not just an address. A range ("A1:B10"), a defined name ("SalesData"), and a sheet-qualified address ("Sheet2!A1") are all admissible in principle. Which grammar INDIRECT accepts in full is broader than the documentation enumerates and is on the unchecked list below.
• Sheet names needing quotes need them in your text. "'Q1 Sales'!A1" — the apostrophes are part of the string you must build, not something INDIRECT adds.
• External workbook references require the other workbook to be open. Microsoft documents that if the source workbook is not open, INDIRECT returns #REF!. This is the single most common production failure of the function: a formula that worked while the source was open breaks silently once it is closed, and no amount of recalculation fixes it.
• Microsoft further documents that external references are not supported in Excel for the web, which makes INDIRECT-based cross-workbook formulas a platform-dependent construct.

The a1 argument is the one people forget exists. INDIRECT("R1C1") with a1 omitted asks Excel to parse R1C1 as an A1-style address — which is a valid A1-style address (column R1C does not exist, but the parse attempt is the point). Notation mismatches here produce #REF! rather than obviously wrong answers, which is at least loud.

Try it

Quirks

Volatility and the dependency graph

This section exists because these are the properties that decide whether you should use the function at all.

Invisibility to the dependency graph. The engine builds its calculation order from references it can see in formulas. It cannot see through text. Two consequences:

1. Restructuring does not update the text. Insert a row above A10 and every literal reference to A10 becomes A11; INDIRECT("A10") still says A10 and now points at different data. Nothing errors. This is the failure mode that makes INDIRECT genuinely dangerous in maintained workbooks — it is silent, and it is discovered by reconciliation rather than by a #REF!.
2. Calculation order cannot be derived. Because the engine does not know in advance what an INDIRECT call will read, it cannot order that call after its true precedents by inspection. Volatility is, in part, how the engine copes: recalculating always is a blunt substitute for knowing when to recalculate.

The upside, stated fairly. INDIRECT does one thing nothing else does: it makes the target of a reference into data. Sheet names in a column, a lookup table chosen by a drop-down, a range whose extent is a user setting — these are genuinely dynamic and INDIRECT expresses them directly. It is also the only way to write a reference that survives row deletion deliberately, which is occasionally exactly what you want. The advice is not "never"; it is "know what you are buying".

Result and edge cases

Return kind: Reference. Because it is a reference and not a value, INDIRECT composes with range-consuming functions — SUM(INDIRECT("A1:A10")) scans a range — and with the range operator.

• Preparation. Unlike most reference-producing functions, INDIRECT is prepared under ValuesOnlyPreAdapter: it takes text and produces a reference, rather than receiving one. It is a reference source, not a reference inspector.
• The text is trimmed and must be non-empty, per the reference implementation's parse; an empty or whitespace-only ref_text is an invalid reference.
• a1 given as a blank or omitted slot. Missing and Empty are distinct at the call boundary (value universe), and the reference implementation treats them differently here — omitted selects A1 style, an explicitly empty argument does not. That is an implementation detail worth probing against Excel rather than assuming.
• Defined names, structured references, spill anchors ("B1#") — each is a distinct reference shape and each is a separate question about what INDIRECT's parser accepts.
• Three-dimensional references ("Sheet1:Sheet3!A1") — likewise.

Errors

Microsoft's page documents:

• #REF! when ref_text is not a valid reference.
• #REF! when ref_text refers to another workbook that is not open.

#REF! is therefore the function's characteristic failure, and note what it does not cover: text that parses fine but points at the wrong place because the sheet was restructured. That case produces a number, not an error.

An error value supplied as ref_text propagates under the universal rule in coercion and lifting — the reference implementation converts it to a coercion failure carrying the incoming code, which is the same discipline.

Whether a non-text ref_text (a number, a logical) is coerced to text and parsed, or rejected outright, is not established here.

How to implement

Notes for implementers

• Parsing is the function. INDIRECT is a reference parser wearing a function's clothes, and its accepted grammar is co-extensive with the host's reference syntax — including quoting rules, sheet-span syntax, structured-reference syntax, and the R1C1 alternative. Implementing it means either reusing the formula parser's reference production or accepting that the two will drift.
• The A1/R1C1 flag changes the grammar, not the formatting. Two parsers, or one parameterized parser; not a post-processing step.
• Locale. Reference syntax is locale-sensitive in places — the sheet separator, list separators inside union expressions, and the spelling of R1C1 in localized builds. Text built in one locale may not parse in another, which makes INDIRECT formulas a portability hazard in a way that literal references are not.
• Caller context is required. An unqualified address resolves against the calling sheet, so the function cannot be evaluated without knowing where it was called from. This is why the reference implementation classifies it CallerContext and why a host-free harness cannot exercise it at all.
• Volatility must be declared, not inferred. An engine that treats INDIRECT as pure will cache a result that is correct only until the workbook changes shape.

Rust code

entries: &[CatalogueEntry {
    id: INDIRECT,
    spelling: "INDIRECT",
    notation: Notation::Call,
    arity: CallableArityShape::range(1, 2),
    parameters: &[Parameter::value("ref_text"), Parameter::value("a1")],
    demand: super::ArgumentDemand::Eager,
    kernel: super::Kernel::Eager(indirect),
}

fn indirect(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(INDIRECT, services);
    Site::finish(resolved(&site, arguments, services))
}

/// INDIRECT's result. `ref_text` is read as text (a number or a logical as its text, a blank
/// cell or an omitted argument as empty text) and `a1` as a logical (omitted from the call: TRUE;
/// written but omitted, or a blank cell: FALSE); an error of `ref_text`, and then of `a1`, is the
/// result before any text is resolved. Text that names no reference is #REF!.
fn resolved(
    site: &Site<'_>,
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Step<CalcValue> {
    site.scalar_operands(arguments)?;
    let text = site.argument(0, coerce::text(&arguments[0]))?;
    let a1 = match arguments.get(1) {
        None => true,
        Some(a1) => site.argument(1, coerce::logical(a1))?,
    };
    let Some(resolution) = services.reference_text() else {
        return Err(site.not_modelled(Some(0)));
    };
    match resolution.resolve(&text, a1) {
        TextReference::Area(area) => Ok(CalcValue::reference(ReferenceLike::area(area))),
        TextReference::Invalid => Err(Exit::Error(WorksheetErrorCode::Ref)),
        TextReference::NotModelled => Err(site.not_modelled(Some(0))),
    }
}

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

• ADDRESS is the natural producer of the text INDIRECT consumes. The pair INDIRECT(ADDRESS(r, c)) is the textbook computed-reference idiom — and, in almost every case, INDEX(range, r, c) does the same job without volatility, without the text round trip, and while remaining visible to the dependency graph. That comparison is the practical bottom line of this page.
• OFFSET computes a reference by displacement rather than by text. It is also volatile, but it is visible to the dependency graph, so it sits between INDEX and INDIRECT on the cost scale.
• INDEX's reference form is the non-volatile alternative for nearly every computed reference that does not need a name to come from data. See the INDEX page.
• Defined names and LET cover cases where the "dynamic" part is really parameterization rather than genuinely data-driven addressing.
• CHOOSE selects among ranges written in the formula. When the candidate ranges are known at authoring time — the usual case for a drop-down-driven lookup — CHOOSE replaces INDIRECT and keeps the references visible.
• FORMULATEXT goes the other way, exposing formula text as data.

Named variants

No named variants are recorded yet.