The INDIRECT function
Turns text into a cell reference, such as "B3" or "Sheet2!A1", and returns what is in that cell. Use it to build a reference from other cells.
Syntax
INDIRECT(ref_text, [a1])- ref_text
- A reference written as text, such as "B3", or built with &, such as "B"&E1.
- a1 (optional)
- TRUE or left out for A1-style text, FALSE for R1C1 style, such as "R3C2".
Examples
D1=INDIRECT("B3")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | 95 | |
| 2 | Jan | 120 | ||
| 3 | Feb | 95 | ||
| 4 | Mar | 140 |
G1=INDIRECT("B"&E1)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Month | Sales | Row | 4 | 140 | ||
| 2 | Jan | 120 | |||||
| 3 | Feb | 95 | |||||
| 4 | Mar | 140 |
G1=SUM(INDIRECT("B2:B"&E1))
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Month | Sales | Row | 4 | 355 | ||
| 2 | Jan | 120 | |||||
| 3 | Feb | 95 | |||||
| 4 | Mar | 140 |
Read the guide
Questions
Why does INDIRECT return
The text is not a valid reference. Check for typos, put sheet names with spaces in single quotes, as in INDIRECT("'Sales 2024'!B2"), and note that a reference to another workbook only works while that workbook is open.
Does INDIRECT update when I insert rows?
No. The text "B3" always means B3, so the reference stays put when rows move. That can be the point, but it also makes INDIRECT easy to break. It recalculates on every change, so use INDEX where you can.