INDIRECT function
Quick answer
INDIRECT turns text into a reference: "B3" becomes the cell B3, and "A2:A"&D1 becomes a range whose last row comes from a cell.
- Syntax
=INDIRECT(ref_text, [a1])- Most common use
=INDIRECT(D2)returns 92 (The value in B3, the address written in D2)
Use it when the address itself is built by a formula. Because the address is text, it doesn't follow inserted rows, and it recalculates after every change, so prefer INDEX or XLOOKUP when they can do the job.
- Available in
- Every current version
- Category
- Lookup functions
- Returns
- A reference to the cell or range named by the text; #REF! if the text isn't a valid address.
- Google Sheets
- Works the same in Google Sheets, including the R1C1 form.
Syntax
=INDIRECT(ref_text, [a1])
| Argument | Required? | What it is |
|---|---|---|
ref_text | Required | The address as text, such as "B3", "A2:A10" or a cell that contains one. |
a1 | Optional | Optional: TRUE or omitted for A1-style text; FALSE for R1C1 style, such as "R3C2". |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =INDIRECT(D2) | 92 | The value in B3, the address written in D2 |
| E3 | =SUM(INDIRECT(D3)) | 255 | The text "B2:B4" used as a range |
| E4 | =INDIRECT(D4,FALSE) | 75 | R4C2 is row 4, column 2: B4 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Name | Score | Address | Result | |
| Ana | 88 | B3 | 92 | |
| Raj | 92 | B2:B4 | 255 | |
| Lee | 75 | R4C2 | 75 |
Practice INDIRECT
Graded instantly, then re-checked on changed data.
Read from an address
D holds cell addresses as text. In E2, return the value at the address written in D2. Fill down to E4.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #REF! | The text isn't a valid address, has a typo, or names a sheet that doesn't exist. =INDIRECT("B3") needs the quotes; =INDIRECT(B3) reads the address written in B3. |
| The formula didn't follow inserted rows | INDIRECT("B3") is text, so inserting a row above doesn't turn it into B4 the way a normal reference would. |
| Slow recalculation | INDIRECT is volatile and recalculates after every change. Use INDEX with MATCH to pick ranges by position. |
Tips
- To sum a column picked in a dropdown, INDEX is sturdier: =SUM(INDEX(B2:D10,0,MATCH(F1,B1:D1,0))).
Questions
What does INDIRECT do in Excel?
It converts text into a cell reference, so you can build addresses with formulas such as ="B"&ROW().
How do I reference another sheet with INDIRECT?
Build the sheet name into the text: =INDIRECT("'"&A1&"'!B2"). The single quotes keep sheet names with spaces working.
Does INDIRECT work with closed workbooks?
No. A reference to another workbook only works while that workbook is open; otherwise it returns #REF!.
Updated Checked by running: every formula on this page was run by our formula engine on .