=INDIRECT(E2) reads the cell whose address is written as text in E2. If E2 says C4, the formula returns the value in C4. The address can also be built from pieces: =INDIRECT("C"&E3) reads column C at the row number in E3.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
F2 reads C4, Carrot's price, 1.10. Change E3 to 2 for Apple's price.
INDIRECT syntax
=INDIRECT(ref_text, [a1])
ref_text: text that spells a reference:"C4","B2:B6","Prices!A2","'Price list'!A2:B9".a1:TRUEor left out for A1-style addresses.FALSEreads R1C1 style, where"R4C3"means row 4, column 3, which suits a row and a column that are both numbers.
If the text is not a valid address, the result is #REF!. INDIRECT returns a real reference, so it works inside SUM, COUNTIF, VLOOKUP and every function that takes a range.
Build a range from numbers
The address can be a whole range. Joining a number into it gives a range whose size comes from a cell.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
With 3 in E2 the text becomes B2:B4, and F2 adds Jan to Mar: 12,900. Set E2 to 6 for the half year, 27,900. 1+E2 is there because the data starts in row 2. The same total can be written without INDIRECT, =SUM(B2:INDEX(B2:B7,E2)), which is not volatile; the OFFSET page compares the options.
Reference a sheet named in a cell
The sheet name can come from a cell too. That turns one summary formula into a lookup across sheets: each row reads the sheet named in column A. Single quotes around the name keep it working for names with spaces.
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
B2 builds the text 'Jan'!B2:B4 and totals it: 12,500. B3 and B4 are the same formula filled down, so they read Feb (12,200) and Mar (13,700). Open the Feb tab and change a number: the summary follows. Type Feb over Jan in A2 and B2 now totals Feb. The B2:B4 inside the quotes is text, so it does not change as the formula fills down; only the A2 reference does.
Dependent drop-down lists
A second drop-down whose items depend on the first is the classic INDIRECT job. In Excel the usual setup is:
- Put each category's items in a column and name each range after its category: select the columns with their headers and use Formulas > Create from Selection > Top row. That creates the names
Fruit,VegetableandDairy. - Give A2 a list of the categories: Data > Data Validation > Allow: List, Source
Fruit,Vegetable,Dairy. - Give B2 a list with the Source
=INDIRECT(A2). When A2 says Fruit, the list reads the range named Fruit.
The sheet below builds the same thing with a sheet per category instead of a named range. D2 uses INDIRECT to spill the items of the sheet named in A2, and B2's list reads D2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
Pick Dairy in A2: D2:D4 switches to Milk, Butter, Cheese, and so do the choices in B2. B2 keeps its old value until you pick a new one; Excel behaves the same way, which is why forms often add a check such as =COUNTIF(D2:D4,B2)>0 beside the item. In Excel 365 you can skip the named ranges and point the second list at a spilled formula, for example =INDIRECT("'"&A2&"'!A2:A4") in a helper cell and =D2# as the Source. The drop-down list page has the rest of the setup.
INDIRECT is volatile, and it ignores inserted rows
Two side effects come from INDIRECT reading text instead of a reference:
- It recalculates on every change. Excel cannot know which cells a piece of text will point to, so it recalculates every INDIRECT after any edit anywhere in the workbook. A few dozen do no harm; tens of thousands make every keystroke slow. INDEX with a row number (
=INDEX(C:C,E3)) gives the same result as=INDIRECT("C"&E3)and only recalculates when its inputs change. - The address does not move. Insert a row above row 4 and
=C4becomes=C5, but=INDIRECT("C4")still reads C4, which is now a different row. Sometimes that is the point, a reference that must stay on a fixed cell whatever happens to the sheet. More often it is a bug waiting for someone to insert a row.
INDIRECT into another workbook only works while that workbook is open; closed, it returns #REF!.
Practice: a price from a row number
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Your turn: In F2, use INDIRECT to return the price in column C on the row number written in E2.
Frequently Asked Questions
What does INDIRECT do in Excel?
It turns text into a reference. =INDIRECT("C4") returns the value of C4, and =INDIRECT(E2) returns the value of whatever cell address is written in E2. The address can be built with &, so =INDIRECT("C"&E2) reads column C at the row number in E2.
How do I reference another sheet whose name is in a cell?
Build the address with the sheet name in single quotes: =INDIRECT("'"&A2&"'!B2"). The quotes keep it working for names with spaces. =SUM(INDIRECT("'"&A2&"'!B2:B4")) totals a range on that sheet.
Why does INDIRECT return #REF!?
The text is not a valid address, or it names a sheet that does not exist, or it points into another workbook that is closed. Check the text the formula builds by putting the same expression in a cell on its own, without INDIRECT.
Is INDIRECT volatile?
Yes. Excel recalculates every INDIRECT on every change anywhere in the workbook, because it cannot tell in advance which cells the text will point to. A few are harmless; thousands slow a workbook down. INDEX can often do the same job without being volatile.
