What it does
In a normal formula, =B5 is fixed inside the formula. INDIRECT takes the address as text, and you can build that text piece by piece with &: =INDIRECT("B"&E2) reads B5 when E2 holds 5. That way a value in another cell decides which cell is read.
The second argument is the address style: TRUE or omitted expects A1 style like "B5", FALSE expects R1C1 style with row and column numbers. ADDRESS, which builds an address from a row and column number, is often used together with INDIRECT.
Like OFFSET, INDIRECT is volatile and recalculates on every change. Because the address is text, it does not shift when rows or columns are inserted; sometimes that is what you want, sometimes it is a surprise. To simply return the value at a position, INDEX is sturdier.
Syntax
=INDIRECT(ref_text, [a1])=DOLAYLI(başv_metni; [a1])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
ref_text | Required | The address as text, or an expression that builds it: "B5", "B2:B"&D2, or a cell that contains an address. |
a1 | Optional | TRUE or omitted: A1 style ("B5"). FALSE: R1C1 style (row 5, column 2). |
Examples
1Build a range from a number in a cell
The months start on row 2. Adding 1 to the number of months in D2 gives the last row, which is appended to the text "B2:B"; INDIRECT turns that text into a range and SUM adds it.
=SUM(INDIRECT("B2:B"&(D2+1)))| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Costs | Months | Total | |
| 2 | Jan | 48,200 | 3 | 146,850 | |
| 3 | Feb | 51,750 | |||
| 4 | Mar | 46,900 | |||
| 5 | Apr | 53,300 | |||
| 6 | May | 49,850 |
D2 says 3, so the text is "B2:B4" and the January–March costs are added: 146,850. The header is on row 1, which is why 1 is added to the number of months.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Read a value from a row and column number
F2 holds a row number and G2 a column number. ADDRESS turns them into an address text (H2); INDIRECT turns that text into a reference and returns the value in the cell (I2).
=ADDRESS(F2,G2)| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Branch | Jan | Feb | Mar | Row | Column | Address | Value | |
| 2 | Camden | 412,000 | 398,500 | 436,200 | 3 | 4 | $D$3 | 365,900 | |
| 3 | Leeds | 356,800 | 371,200 | 365,900 | |||||
| 4 | Bristol | 298,400 | 305,100 | 322,750 |
- H2
=ADDRESS(F2,G2) - I2
=INDIRECT(H2)
Row 3, column 4 gives the address $D$3; INDIRECT returns the value there, Leeds's March sales: 365,900.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #REF!
Cause: The text is not a valid address: for example the column letter and row number were never joined, the cell holding the address is empty, or the sheet it names does not exist.
Fix: Build the address text in a separate cell first and look at it; make sure it is a complete address such as "B5".
- Reference does not move
Cause: After inserting a row above,
INDIRECT("B5")still reads B5 although the data moved to B6.Fix: If you do not want this, use a normal reference or INDEX instead of fixed text.
- Slow workbook
Cause: Many INDIRECT formulas recalculate on every change.
Fix: Prefer INDEX where it can do the same job.
Good to know
- Mind the quotes:
INDIRECT("B5")reads B5, whileINDIRECT(B5)reads the address written in B5. - When you first build the address text, write the formula without INDIRECT and look at the result (
="B2:B"&(D2+1)); once the text is right, wrap it in INDIRECT.
In Nexell
Get Nexell to do it from a sentence
Next to the monthly cost table, add a summary cell that totals the costs up to the number of months I type
Convert to tableBring your table into the tool and type this. Nexell builds the total with real Excel formulas and shows it in the preview; in the downloaded file, the total changes when you change the number of months.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I get data from another sheet with INDIRECT?
Add the sheet name and an exclamation mark to the address text: =INDIRECT("'"&A2&"'!B5") reads cell B5 of the sheet named in A2. The single quotes are needed when the sheet name contains spaces; if the sheet is renamed or deleted, you get #REF!.