Lookup & reference
How to Use INDIRECT in Excel (Text to References)
INDIRECT turns a text string into a real cell reference. Instead of pointing Excel at A5 directly, you hand it the text "A5" and INDIRECT resolves it into the live cell, so the reference can be assembled on the fly.
It shines when you want to build a reference from other cells, pull from a sheet whose name is chosen by a dropdown, or lock a range so it survives inserted rows. This guide covers the syntax, worked examples, and the #REF! pitfalls.
INDIRECT syntax
=INDIRECT(ref_text, [a1]) | Argument | Required | What it does |
|---|---|---|
ref_text | Required | A text string that spells out a reference, such as "A5", "Sheet2!B2", or a named range. |
a1 | Optional | TRUE/omitted treats ref_text as A1-style; FALSE treats it as R1C1-style. |
Note: INDIRECT is a volatile function: it recalculates on every edit. It also cannot read a closed workbook. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Build a cell reference from another cell
=INDIRECT("A" & B1) If B1 holds 5, "A" & B1 becomes the text "A5", and INDIRECT resolves it to the live cell A5. Change B1 to 7 and it instantly points at A7.
2. Pull a value from a sheet chosen by name
=INDIRECT(A1 & "!B2") If A1 contains the text January, this reads January!B2. Drive A1 with a dropdown to switch sheets without rewriting the formula. Wrap the name in apostrophes if it contains spaces.
3. Sum a range named in text
=SUM(INDIRECT(C1)) If C1 holds "A1:A10", INDIRECT hands that range to SUM. This lets a single formula total different ranges depending on what C1 says.
4. Keep a reference fixed when rows are inserted
=SUM(INDIRECT("A1:A10")) Because the range is text, Excel never shifts it when you insert or delete rows. The formula keeps pointing at the literal cells A1:A10.
How to write INDIRECT step by step
- 1
Click the result cell and type =INDIRECT(
- 2
Type the reference as text in quotes, for example "A5", or build it from cells with the & operator.
- 3
To target another sheet, join the sheet name and the cell, such as A1 & "!B2".
- 4
Optionally add a comma and FALSE to read the text as R1C1 style instead of A1 style.
- 5
Close the bracket ) and press Enter. Wrap it in SUM or AVERAGE to work on a range.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#REF! | The text does not resolve to a valid reference, often a misspelled sheet name, a missing named range, or a stray space. | Check the assembled text spells a real reference, match the sheet name exactly, and wrap names with spaces in apostrophes. |
#NAME? | The reference text is malformed, such as a missing quote mark or an invalid name. | Make sure literal references are in quotes and that any named range referred to actually exists. |
Blank or stale result | INDIRECT is pointing at a closed external workbook, which it cannot read. | Open the source workbook, or use a direct link instead, since INDIRECT only reads open files. |
Slow workbook | INDIRECT is volatile, so many of them recalculate on every edit. | Limit how many INDIRECT formulas you use, or switch to a direct reference where the address never changes. |
Frequently asked questions
What does INDIRECT actually do?
INDIRECT converts a text string into a live cell reference. You give it text like "A5" or "Sheet2!B2" and it returns the value at that location, which lets you build references from other cells on the fly.
Why is my INDIRECT returning #REF!?
The text did not resolve to a valid reference. Common causes are a misspelled or missing sheet name, a named range that does not exist, or a sheet name with spaces that was not wrapped in apostrophes.
Is INDIRECT volatile?
Yes. INDIRECT recalculates on every change in the workbook, which can slow large files. Use it where you truly need a dynamic reference, and prefer direct references elsewhere.
Can INDIRECT reference another workbook?
Only if that workbook is open. INDIRECT cannot read a closed external file, so it returns #REF! until the source workbook is opened.