Excel INDIRECT Function

INDIRECT converts a text string into a cell reference that Excel treats as real, not as a label. That distinction is the entire function: type =A1 and Excel reads a reference; type ="A1" and Excel reads four characters. INDIRECT bridges the gap, letting you construct references out of text and concatenation, then hand the result to any formula that expects a range.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and all earlier desktop versions. INDIRECT has been part of Excel since the earliest spreadsheet versions and behaves the same in Excel Online, with one exception covered further down for R1C1-style references on the web.

The tradeoff: INDIRECT is volatile. It recalculates on every worksheet change, not just when its own inputs change. In a large workbook, this can slow things down noticeably. This article assumes you're comfortable with named ranges, structured references, and nested formulas.

Syntax

=INDIRECT(ref_text, [a1])

Parameter	Required	Description
ref_text	Yes	The text string to convert into a reference. Can be a literal string, a cell that contains a reference as text, or a concatenated expression built from multiple cells.
a1	No	Controls whether ref_text is interpreted as A1-style ("Sheet1!B4") or R1C1-style ("R4C2"). TRUE or omitted reads A1-style. FALSE or 0 reads R1C1-style.

Basic Example

You're building a rolling monthly summary where each month's actuals live on its own sheet, Jan, Feb, Mar, and so on, and a control cell lets you pick which month to pull from.

=INDIRECT(B2 & "!D10")

// B2 contains the sheet name as text, e.g. "Mar"
// "!D10" is appended text that points to cell D10 on that sheet

Change B2 from "Jan" to "Mar" and the formula instantly repoints itself to Mar!D10, no editing required. INDIRECT concatenates the sheet name with the cell address, evaluates the whole string as "Mar!D10", and returns whatever value sits in that cell. Nothing else on the sheet changes. Only the reference target does.

How INDIRECT Works

It reads text, it doesn't calculate formulas

INDIRECT only converts a string into a reference. It does not evaluate formula syntax inside that string. =INDIRECT("SUM(A1:A10)") fails. INDIRECT has no idea what SUM means. It's not a formula parser, just a reference builder. Feed it something that resolves to an address, like "A1:A10", "Sheet2!B4", or "Sales" as a named range, and it works.

The a1 argument switches reference dialects

Leave a1 out and INDIRECT assumes A1-style: column letters, row numbers, Sheet1!B4. Set it to FALSE and INDIRECT switches to R1C1-style, where "R4C2" means row 4, column 2. That's the same cell as B4. This matters more than it sounds. Excel normally won't let you mix A1 and R1C1 notation in formulas on the same sheet, but INDIRECT ignores that restriction entirely. You can run an A1-style INDIRECT formula in B2 and an R1C1-style one in B3 on the identical sheet, no settings change required.

Named ranges resolve the same way sheet references do

ref_text doesn't have to be a cell address. It can be the name of a defined range: =INDIRECT("Q3Budget") returns whatever Q3Budget refers to, exactly as if you'd typed the name directly. This is what makes INDIRECT useful for scenario switching. A dropdown cell holding "Q1Budget", "Q2Budget", or "Q3Budget" lets one formula pull from three entirely different named ranges.

It scopes to sheets, workbooks, and tables, but only if they're open

INDIRECT can reach across sheets ("Sheet2!A1"), across open workbooks ("[Budget.xlsx]Sheet1!A1"), and into Excel Table columns ("Sales[Amount]"). The workbook requirement is the catch: reference a closed workbook and INDIRECT throws #REF! immediately. There's no fallback and no cached value. The source has to be open in the same Excel session.

Common Use Cases

Switching data source with a dropdown

Pair INDIRECT with a data validation dropdown to let users pick which sheet or named range feeds a summary cell.

=INDIRECT(SelectedRegion)   // SelectedRegion is a cell holding "NorthSales", "SouthSales", etc.

Each option in the dropdown matches a named range. Change the selection, the summary updates. No VBA, no separate formula per region.

Summing non-contiguous ranges built from text

Combine INDIRECT with SUMPRODUCT to total ranges that shift based on other inputs, without writing a separate SUM for each possibility.

=SUMPRODUCT(INDIRECT("Jan!C2:C50"), INDIRECT("Feb!C2:C50"))

// Multiplies matching rows across two sheets, then sums the products

Feeding INDIRECT into dynamic array functions

This is where most tutorials stop, and it's worth pushing further. INDIRECT returns a reference, and FILTER, UNIQUE, and SORT all accept a reference as their array argument, including one INDIRECT just built.

=UNIQUE(INDIRECT(SheetName & "!A2:A500"))

// Returns distinct values from column A of whatever sheet SheetName points to

Wrap the whole thing in LET if the same reference gets reused across a formula:

=LET(
  src, INDIRECT(SheetName & "!A2:D500"),
  FILTER(src, INDEX(src, 0, 3) > 1000)
)

// Builds the reference once with LET, then filters it without recalculating INDIRECT twice

Incrementing a reference with CELL

To step through a sequence of cells based on a growing text string, pair INDIRECT with CELL's address output rather than hardcoding each cell.

=INDIRECT($B$5 & "!" & CELL("address", A1))

// $B$5 holds a sheet name; CELL("address", A1) returns "$A$1" as text

Drag this down and the CELL reference advances with each row, while the sheet name stays fixed.

Handling Errors

INDIRECT throws #REF! when ref_text doesn't resolve to something that exists. Unlike most lookup functions, it doesn't return #N/A. A broken reference is always #REF!.

Common causes of #REF!:

• The source workbook is closed (INDIRECT requires it to be open)
• The sheet name is misspelled, or the referenced sheet was renamed or deleted
• A sheet name containing a space or punctuation isn't wrapped in single quotes — Q1 Sales!A1 needs to be 'Q1 Sales'!A1 inside the text string
• The named range in ref_text no longer exists, or was typed with a typo
• ref_text evaluates to an empty string or a value that isn't a valid address at all

Wrapping INDIRECT in IFERROR hides the #REF! error without telling you which of the five causes above triggered it. Diagnose first: build the ref_text string in a helper cell (=B2&"!D10") and confirm it reads exactly as expected, correct sheet name, correct quoting, source workbook open, before deciding a fallback value is the right long-term fix.

=IFERROR(INDIRECT(B2 & "!D10"), "Sheet not found")

Use this once you've confirmed the failure is a genuine missing sheet or closed workbook, not a typo sitting in the helper cell. Swallowing the error before checking is how a broken reference turns into "Sheet not found" for months, with nobody tracing it back to a misspelled tab name.

Notes & Gotchas

What is the INDIRECT function in Excel and how is it used?

INDIRECT takes a text string and converts it into a working cell reference. It's used whenever the reference itself needs to change based on other inputs, a dropdown selection, a concatenated sheet name, a named range built from a variable, rather than staying fixed the way a typed reference does.

How do you use INDIRECT to reference another sheet or workbook in Excel?

For another sheet in the same workbook, concatenate the sheet name with an exclamation mark and the cell address: =INDIRECT("Sheet2!A1"). For a different workbook, wrap the file name in square brackets and quote the whole path: =INDIRECT("'[Budget.xlsx]Sheet1'!A1"). The external workbook must be open. INDIRECT can't read from a closed file, no matter how correct the path text is.

What is the difference between INDIRECT and a direct cell reference in Excel?

A direct reference (=A1) is fixed at the moment you write the formula and only updates if you edit the formula itself. INDIRECT builds the reference at calculation time from a text string, so the same formula can point at a different cell, sheet, or range every time it recalculates. The cost is volatility. INDIRECT recalculates on every workbook change; a direct reference only recalculates when its actual dependents change.

Why does the INDIRECT function return a #REF! error?

#REF! means the text INDIRECT tried to evaluate doesn't point to a real, currently accessible location. The three most common triggers: the source workbook is closed, the sheet or range name was misspelled or deleted, or a sheet name with a space is missing its required single quotes. Check the raw text string first, build it in a helper cell and read it literally, before assuming the formula logic is wrong.

What is the difference between the A1-style and R1C1-style reference in the INDIRECT function?

A1-style uses column letters and row numbers ("B4", "Sheet1!B4") and is what the a1 argument defaults to when omitted or set to TRUE. R1C1-style uses row and column numbers ("R4C2") and requires setting a1 to FALSE. Excel normally forbids mixing the two styles on one sheet through the interface, but INDIRECT ignores that rule. You can run an A1-style INDIRECT formula and an R1C1-style one on the same sheet without changing any workbook settings.

Does INDIRECT work with Excel Tables and structured references?

Yes, but the syntax is stricter than a normal range reference. =SUM(INDIRECT("Sales[Amount]")) works when Sales is a real Table name and Amount a real column header, but the text has to match exactly. INDIRECT won't autocomplete or validate structured references the way typing them directly does. If the table name changes, every INDIRECT string referencing it breaks silently until recalculated, since there's no red squiggle or immediate error to flag the mismatch.

Does INDIRECT work the same way in Excel Online and on Mac?

Mostly, with one practical difference worth knowing. R1C1-style references inside INDIRECT can behave inconsistently in Excel for the web depending on the workbook's display settings, so formulas that work perfectly in desktop Excel sometimes need retesting after uploading to SharePoint or OneDrive. Cross-workbook INDIRECT references are also more fragile online, since "keeping the source workbook open" doesn't map cleanly to a browser tab the way it does to a desktop window.

Can INDIRECT be used inside FILTER, UNIQUE, or LET?

Yes. INDIRECT returns a reference, and any function that accepts a reference as an argument accepts one built by INDIRECT. =UNIQUE(INDIRECT("Sheet2!A2:A100")) works exactly as expected. The one thing that doesn't change: INDIRECT still recalculates volatilely inside a dynamic array formula, so wrapping it in LET reduces redundant calls within one formula but doesn't reduce how often Excel recalculates it across the workbook.

Is INDIRECT a security or sharing risk in shared workbooks?

It can be, in a narrower sense than most people expect. INDIRECT-built references to external workbooks depend on file paths that may not exist on a colleague's machine, so a workbook that works fine for you can throw #REF! for everyone else the moment it's shared. There's no malicious code risk the way there is with macros, but the fragility of hardcoded external paths inside INDIRECT strings is a real handoff problem worth documenting if you build this into shared files.

Related Functions

Function	Use this when...
INDEX	You want dynamic-range behavior without the volatility. INDEX rebuilds references without recalculating on every workbook change.
VLOOKUP	The reference target is fixed and you just need to look up a value, with no need to construct it from text at all.