How to Use the INDIRECT Function in Google Sheets
=INDIRECT(cell_reference_as_string, [is_A1_notation]). =INDIRECT("A2") points at cell A2.
What INDIRECT does
A normal reference such as A2 is fixed. INDIRECT lets a formula build the reference from text, so you can change which cell or sheet is read by editing another cell. This powers things like a dropdown that decides which sheet a summary reads from.
INDIRECT syntax
=INDIRECT(cell_reference_as_string, [is_A1_notation])
cell_reference_as_string: required. The reference, written as text inside quotation marks, or taken from a cell that holds such text.is_A1_notation: optional. The default is TRUE (A1-style). Set FALSE for R1C1-style references. Google's page names this argument differently in the syntax line and in the argument list, but the meaning is the same.
The returned reference can be a single cell or a range.
A step-by-step example
Cell A2 holds 50, cell A3 holds 80 and cell B2 holds the text A3.
| A | B | |
|---|---|---|
| 1 | Value | Reference |
| 2 | 50 | A3 |
| 3 | 80 |
=INDIRECT(B2)
This returns 80. Here is why: B2 contains the text A3, INDIRECT turns that text into a reference to cell A3, and the value there is 80. Change B2 to A2 and the result becomes 50.
R1C1 notation
With FALSE as the second argument, the reference is read as row and column numbers. Google's sample INDIRECT("R2C3", FALSE) returns cell C2, row 2, column 3. By the same rule INDIRECT("R2C1", FALSE) points at A2.
Sheet names from cells
The help page also shows INDIRECT("Sheet2!"&B10), which joins a sheet name and the text in B10 into one reference.
Common mistakes
- Forgetting the quotation marks around a typed reference. Without them, the formula reads the cell's value instead.
- Mixing the notations. R1C1 text needs FALSE as the second argument.
- Overusing it. References built from text are harder to audit than normal ones.
Try it yourself
Use the table above (A1:B3). Type your formula into the box, starting with =, then press Check.
Exercise 1: Reference from a cell
Write a formula that returns the value of the cell whose address is written as text in B2.
Exercise 2: Row and column numbers
Write a formula that uses R1C1 notation to return row 2, column 1.
What to learn next
- CHOOSE: pick a value by its position.
- MATCH: find the position of a value.
- VLOOKUP: fetch one value from a table.
Syntax and rules in this guide were checked against Google's INDIRECT help page. See all guides.