INDIRECT function
INDIRECT turns text into a live cell reference, so you can build sheet or cell addresses on the fly, for example a dropdown that switches sheets. It is volatile and can be slow in big workbooks.
Example
Read it as a sentence: treat the text Items!D2 as a cell address and give me the value in that cell, which is 12.
Syntax
INDIRECT(ref_text, [a1])
Arguments in [square brackets] are optional.
-
ref_text(required) - Text that describes a reference, such as "B5" or "Sheet2!A1".
-
a1(optional) - TRUE or omitted for A1 style, FALSE for R1C1.
Returns: The reference named by the text.
INDIRECT is volatile: Excel works it out again every time anything in the workbook changes.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=INDIRECT("Items!D2") | 12 | A1 text |
=INDIRECT("Data!R2C3",FALSE) | 0 | R1C1 text |
=INDIRECT("data!c2") | 0 | Lower case |
=INDIRECT("'Data'!C2") | 0 | Quoted sheet |
=SUM(INDIRECT("Items!D2:D7")) | 68 | Range text |
=INDIRECT("Data!"&"F1") | 0 | Blank target |
=INDIRECT("abc") | #REF! | Not a reference |
=INDIRECT("") | #REF! | Empty text |
The Items sheet these formulas read
The Data sheet these formulas read
A cell that starts with = holds that formula.
Watch out for
- Volatile and slow in big workbooks.
- Breaks if you rename the sheet or the text is wrong, since Excel will not update it.
- Cannot read closed workbooks.
Where it works
- Excel
- INDIRECT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .