Cell named by text
| A | B | |
|---|---|---|
| 1 | Item | Sales |
| 2 | Pens | 40 |
| 3 | Ink | 90 |
| 4 | Pads | 25 |
Formula: D2: =INDIRECT("B"&3)
Result: 90
The text "B3" becomes a reference to the second sales figure.
Google Sheets function
INDIRECT in Google Sheets
Google Sheets INDIRECT takes an optional second argument: FALSE makes the text be read as an R1C1 reference.
In Google Sheets
It cannot point into a different spreadsheet file; use IMPORTRANGE for that.
The arguments and common errors are covered in the INDIRECT Excel guide; check the function help in Google Sheets for its own argument list.
Worked examples in Google Sheets
Reference stored in a cell
| A | B | |
|---|---|---|
| 1 | Target | Value |
| 2 | B3 | 90 |
| 3 | 25 |
Formula: D2: =INDIRECT(A2)
Result: 25
A2 holds the text B3; INDIRECT reads the value stored at that address.
A range of variable length
| A | B | |
|---|---|---|
| 1 | Rows | Sales |
| 2 | 3 | 40 |
| 3 | 90 | |
| 4 | 25 |
Formula: D2: =SUM(INDIRECT("B2:B"&A2))
Result: 130
With 3 in A2 the text is "B2:B3", which adds 40 and 90.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =INDIRECT("B"&4)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Item | Price | Stock | Result | |
| 2 | 101 | Pen | 2.5 | 120 | Lamp | |
| 3 | 102 | Notebook | 4 | 80 | ||
| 4 | 103 | Lamp | 25 | 15 | ||
| 5 | 104 | Mug | 7.5 | 40 | ||
| 6 | 105 | Backpack | 39 | 12 |
Result in F2: Lamp
Frequently asked questions
- Can INDIRECT read another Google Sheets file?
- No, INDIRECT works within the current spreadsheet. Bring in data from another file with IMPORTRANGE.
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.