The INDIRECT function in Excel returns the reference named by a text string, so if A2 holds the text B2, =INDIRECT(A2) shows whatever's in cell B2. Microsoft says to use it when you want to change which cell a formula points at without changing the formula itself.
Most formulas point straight at a cell. INDIRECT points at a cell that holds the address, as text. Change that text and the formula follows it, without anyone editing the formula. That's what makes it useful, and it's also what makes it fragile, which I'll come to.
INDIRECT syntax
The syntax is INDIRECT(ref_text, a1), and only the first argument is required:
| Argument | Required? | What it holds |
|---|---|---|
| ref_text | Yes | A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string |
| a1 | No | TRUE or left out reads ref_text as A1-style; FALSE reads it as R1C1-style |
Microsoft adds that the references are evaluated immediately to display their contents, so you see the value straight away, not the address.
Microsoft's four INDIRECT examples
| Formula | The cell it reads holds | Result (Microsoft's example) |
|---|---|---|
=INDIRECT(A2) | The text B2 | 1.333, the value in B2 |
=INDIRECT(A3) | The text B3 | 45, the value in B3 |
=INDIRECT(A4) | The text George, a defined name for B4 | 10, the value in B4 |
=INDIRECT("B"&A5) | The number 5 | 62, the value in B5 |
The last one is the most useful. "B"&A5 joins the letter B and the 5 in A5 into B5, and INDIRECT reads that cell. Change A5 to another row number and the same formula reads a different row. The George example shows the other trick: a defined name works too, so the text in your cell can be a name rather than an address. My guide to creating a named range in Excel shows how to set one up.
Why INDIRECT returns #REF!
| Cause (Microsoft) | What to check |
|---|---|
| ref_text isn't a valid cell reference | The text in the cell is a real address or a defined name |
| ref_text points to another workbook that's closed | The other workbook is open |
| The range is outside the 1,048,576 row or 16,384 column (XFD) limit | The address you've built is inside the sheet |
Microsoft also notes that external references aren't supported in Excel Web App, so a formula that reads another workbook won't work there.
There's a quieter risk too. Because INDIRECT only sees text, Excel doesn't treat that text as a link to the target cell. Rename the sheet or move the cell, and a normal formula would update itself, but the text inside your INDIRECT formula stays the same, so it points at the wrong place or returns #REF!.
ADDRESS and OFFSET: the related functions
ADDRESS works the other way round from INDIRECT: it builds a reference as text from row and column numbers. ADDRESS(2,3) returns $C$2 and ADDRESS(77,300) returns $KN$77. Its abs_num argument sets the type of reference:
| abs_num | Reference type |
|---|---|
| 1 or left out | Absolute |
| 2 | Absolute row, relative column |
| 3 | Relative row, absolute column |
| 4 | Relative |
OFFSET returns a reference rather than text. The syntax is OFFSET(reference, rows, cols, height, width), with height and width optional, and it returns a range a set number of rows and columns away from a starting cell. Positive rows count down from the start and negative rows count up. The starting reference has to be a cell or a range of adjacent cells, or OFFSET returns #VALUE!.
How to use INDIRECT, step by step
- Put a reference as text in a cell, for example B2, or build one with
"B"&A5. - In another cell, type
=INDIRECT(and point at the cell holding the text. - Close the bracket and press Enter to see the value it points to.
- If you see #REF!, check the text is a valid reference and that any other workbook is open.
If what you're after is pulling a matching value out of a table, INDIRECT isn't the tool. Use VLOOKUP instead.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
My take on INDIRECT
INDIRECT is powerful in sheets where a dropdown or a typed value decides which cell to read. It's also fragile, because Excel can't follow a reference that's only text. Use it deliberately, keep a note of where you've used it, and check those formulas whenever you rename a sheet or move data around.
My notes on a nearby topic are in the guide on how to use the TEXT function in Excel.
Frequently asked questions
What does INDIRECT do in Excel?
It returns the reference specified by a text string, so a formula can read whichever cell the text names.
Why does INDIRECT return #REF!?
The text isn't a valid reference, it points to a closed workbook, or it's outside the row or column limits.
Can INDIRECT use a named range?
Yes. In Microsoft's example the text George, a defined name for B4, returns the 10 held in B4.
What is the a1 argument in INDIRECT?
It's optional. TRUE or left out reads the text as an A1-style reference; FALSE reads it as R1C1-style.
Does INDIRECT work with other workbooks?
Only if that workbook is open, and Microsoft says external references aren't supported in Excel Web App.
