Home › Excel functions › INDIRECT

INDIRECT Function in Excel, with Examples

The INDIRECT function turns text into a real cell reference. =INDIRECT("B3") returns whatever is in B3. That becomes powerful when you build the text with a formula: pick a sheet name, a row number or a range name from a cell, and the formula follows.

Excel CSE · 3 min read
In this guide
  1. INDIRECT syntax
  2. Basic example
  3. Data from another sheet
  4. Dependent drop-down list
  5. Downsides
  6. FAQ

INDIRECT syntax

=INDIRECT(ref_text, [a1])
ArgumentWhat it means
ref_textA reference as text, such as "B3", "Sheet2!A1" or a named range.
a1
(optional)		TRUE or left out for normal A1 references; FALSE for R1C1 style ("R3C2").

Basic example: row number from a cell

ABCD
110Row3
220
330
440
550
=INDIRECT("A" & D1)

"A" & D1 becomes the text "A3", and INDIRECT returns the value in A3: 30. Change D1 to 5 and the result becomes 50.

The same trick makes a range that grows with a number:

=SUM(INDIRECT("A1:A" & D1))

With 3 in D1 this adds A1:A3: 10 + 20 + 30 = 60.

Pull data from a sheet named in a cell

Say you have one sheet per month (Jan, Feb, Mar) with the total in B10. On a summary sheet, put the sheet names in column A and use:

=INDIRECT("'" & A2 & "'!B10")

Fill down and each row shows the total from the sheet named in column A. The single quotes around the sheet name are needed when a name contains spaces, such as 'Sales 2026', and do no harm otherwise.

INDIRECT is the standard way to make a second drop-down that depends on the first: pick "Fruit" and the next list shows only fruit.

  1. Put each list in its own column with a heading: Fruit, Vegetables.
  2. Select each list (without the heading) and name it in the Name Box, exactly like the heading: Fruit, Vegetables.
  3. In A2, create a drop-down list with the source Fruit,Vegetables.
  4. In B2, create a drop-down list with the source =INDIRECT(A2).

Range names can't contain spaces. For "Dairy products", name the range Dairy_products and use =INDIRECT(SUBSTITUTE(A2, " ", "_")).

Downsides of INDIRECT

  • It's volatile. INDIRECT recalculates every time anything changes, which slows down big workbooks.
  • References don't update. Because the reference is text, it doesn't move when you insert rows or rename a sheet. Renaming "Jan" to "January" gives #REF!.
  • It can't read closed workbooks. A reference to another file only works while that file is open.

For lookups, INDEX/MATCH or XLOOKUP is usually the better choice. Use INDIRECT when the sheet or range name itself has to change.

Frequently asked questions

Why does INDIRECT return #REF!?

The text isn't a valid reference: a typo in the sheet name, missing quotes around a name with spaces, a named range that doesn't exist, or a reference to a closed workbook.

Can I use INDIRECT with a table name?

Yes, for example =SUM(INDIRECT(A2 & "[Amount]")) with a table name in A2.

What does INDIRECT do with a fixed reference?

=INDIRECT("A1") always points to A1, even if you insert rows above it. That's a way to lock a reference completely, beyond what $A$1 does.

Is there an alternative to INDIRECT that isn't volatile?

INDEX is non-volatile: =SUM(A1:INDEX(A:A, D1)) gives the same growing range as the INDIRECT example.