INDIRECT Function in Google Sheets
Converts a text string into a cell reference. Lets you build dynamic references based on cell values — powerful for templated sheets but also a common cause of slowdowns when overused.
Syntax
INDIRECT(reference, [is_A1_notation])Returns: The value or range at the address text.
Excel equivalent: INDIRECT (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| reference | Required | A text string representing a cell or range, e.g., "A1", "Sheet2!B2:B100", or "R1C1" notation. |
| is_A1_notation | Optional | TRUE (default) for standard A1 notation. FALSE for R1C1 notation. |
Examples
Reference a sheet name from a cell
=INDIRECT(A1 & "!B2")If A1 contains "Q1", returns Q1!B2. Lets you switch sheets via a dropdown.
Sum a column whose name is in a cell
=SUM(INDIRECT(A1 & ":" & A1))If A1 contains "B", SUMs the whole B column. Builds the range string dynamically.
Build a range from numeric inputs
=INDIRECT("A" & B1 & ":A" & B2)If B1 = 2 and B2 = 100, returns A2:A100. Useful when bounds come from user input.
When to use an alternative
- OFFSET — You want to compute the range from an anchor and offsets without string building.
- INDEX — You want a positional lookup with cleaner syntax.
- Named Ranges — The dynamic part is structured and predictable — name your ranges and reference the names.
Common errors and how to fix them
#REF!
Cause: Sheet name has spaces or special characters — needs single quotes inside INDIRECT.
Fix: Wrap sheet name with quotes: INDIRECT("'My Sheet'!A1").
Slow sheet
Cause: INDIRECT is volatile — it recalculates on every change, even unrelated edits.
Fix: Limit how many INDIRECT calls live in the sheet. Cache the result if reading the same range many times.
Related functions
Frequently Asked Questions
Is INDIRECT volatile?
Yes — every edit anywhere in the sheet causes INDIRECT to recalculate, which can slow large sheets. Prefer named ranges or INDEX when possible. Reserve INDIRECT for places where the dynamic reference is essential.
How do I reference another spreadsheet with INDIRECT?
You can't directly — INDIRECT only works within the current spreadsheet. For cross-spreadsheet dynamic references, combine IMPORTRANGE with a URL stored in a cell.