The INDIRECT function

Turns text into a cell reference, such as "B3" or "Sheet2!A1", and returns what is in that cell. Use it to build a reference from other cells.

How to use INDIRECT

INDIRECT builds a cell reference out of text. A normal reference points straight at a cell. INDIRECT works more like a street address you put together from pieces: you hand it text such as "B3", and it goes to that address and fetches whatever is there.

The point is that you can build the text from other cells with &. If E1 holds 4, =INDIRECT("B"&E1) returns the value in B4, and =SUM(INDIRECT("B2:B"&E1)) adds B2 down to the row you typed in E1. Change E1 and the formula follows, without you editing it.

Typical jobs are pulling the same cell from a sheet whose name sits in a cell, as in =INDIRECT("'"&A2&"'!B2"), summing a range whose size you choose, or turning a name picked in a drop-down into the range it stands for. That last trick powers dependent drop-down lists.

INDIRECT has a price. It recalculates on every change, it does not follow when you insert or delete rows, and a typo in the text only shows up afterward. If you just need the value at a given row or column position, INDEX does the job without those drawbacks.

Syntax

INDIRECT(ref_text, [a1])
ref_text
A reference written as text, such as "B3", or built with &, such as "B"&E1.
a1 (optional)
TRUE or left out for A1-style text, FALSE for R1C1 style, such as "R3C2".

Examples

D1=INDIRECT("B3")
ABCD
1MonthSales95
2Jan120
3Feb95
4Mar140
G1=INDIRECT("B"&E1)
ABCDEFG
1MonthSalesRow4140
2Jan120
3Feb95
4Mar140
G1=SUM(INDIRECT("B2:B"&E1))
ABCDEFG
1MonthSalesRow4355
2Jan120
3Feb95
4Mar140

Common INDIRECT mistakes

  • INDIRECT returns #REF! when I use a sheet name

    Sheet names with spaces or special characters need single quotes around them inside the text: =INDIRECT("'"&A2&"'!B2"). Also check that the sheet exists and that the name is spelled exactly the same.

  • INDIRECT(B3) returns #REF!

    INDIRECT wants text that reads as a reference. If B3 holds the number 95, =INDIRECT(B3) tries to read 95 as an address. Put quotes around a literal reference, =INDIRECT("B3"), or point at a cell that contains the address as text.

  • Every row shows the same cell after I fill down

    The text "B2" never changes when you copy the formula, unlike a normal reference. Build the address from something that does change, such as the row number: =INDIRECT("B"&ROW()).

  • My workbook got slow

    INDIRECT recalculates on every change anywhere in the workbook. Where you only need a row or column position, switch to INDEX: =INDEX(B:B,E1) instead of =INDIRECT("B"&E1).

Read the guide

Questions

Why does INDIRECT return #REF!?

The text is not a valid reference. Check for typos in the address or the sheet name. A reference to another workbook only works while that workbook is open.

Does INDIRECT update when I insert rows?

No. The text "B3" always means B3, so the reference stays put when rows move. That can be the point, but it also makes INDIRECT easy to break. It recalculates on every change, so use INDEX where you can.

How do I pull data from another sheet with INDIRECT?

Build the address with the sheet name, =INDIRECT("'"&A2&"'!B2"), where A2 holds the sheet name. The single quotes make it work for names with spaces too.

How do I make a dependent drop-down list with INDIRECT?

Name each list after a choice in the first drop-down, with no spaces. Make the first drop-down with data validation, then in the second one choose List and enter =INDIRECT(A2) as the source, where A2 is the cell with the first choice.

Related functions