Build References from Text with INDIRECT

All versionsINDIRECT

INDIRECT turns a text string into a real cell reference — so a formula can point at a cell, range, or sheet chosen on the fly. It’s how you build dynamic references from other cells’ values.

Quick formula: to reference the cell whose address is built from D1 (a column) and D2 (a row):

=INDIRECT(D1 & D2)

If D1 is “B” and D2 is 5, INDIRECT returns the value of B5 — the text becomes a live reference.

Functions used (tap for the full reference guide):

INDIRECT ADDRESS

The example

Pick a column and row; INDIRECT returns that cell’s value.

	A	B	D	E
1		Data	Build ref	Value
2	row4	10	B	30
3	row5	20	3
4	row6	30	(=INDIRECT("B"&3))

The formula

Reference cell B + a row number from another cell:

=INDIRECT("B" & D2) // D2 = 3 → value of B3

How it works

INDIRECT converts text into a reference:

You build the address as text — "B" & D2 makes the string "B3".
INDIRECT(…) interprets that text as an actual cell reference and returns its value.
Because the address comes from cells, the reference is dynamic — change D2 and the formula points somewhere new.
It also builds sheet references (INDIRECT(sheet & "!A1")) and works with named ranges by name.

INDIRECT is volatile and can’t cross closed workbooks. It recalculates on every change (slow in bulk) and only resolves references in open files. Use it where you truly need a text-built reference, not as a default.

Try it: interactive demo

Live demo

Column B holds 10/20/30 in rows 2,3,4. Pick a row.

Row number

Value:

Variations

Dynamic sheet reference

Pull A1 from the sheet named in D1:

=INDIRECT(D1 & "!A1")

R1C1 style

Build a reference by row/column number:

=INDIRECT("R" & D2 & "C" & D3, FALSE)

Reference a named range by text

Pick a named range from a cell:

=SUM(INDIRECT(D1))

Pitfalls & errors

#REF! from a bad string. If the built text isn’t a valid reference (typo, missing sheet), INDIRECT errors. Double-check the pieces you concatenate.

Volatile = slow. Thousands of INDIRECTs recalc on every edit. Prefer INDEX/CHOOSE for picking among a fixed set of ranges.

Breaks on closed workbooks and some structured refs. INDIRECT can’t read closed files and doesn’t play well with all Table references.

Practice workbook

📊

Download the free Build References from Text with INDIRECT practice workbook

Live INDIRECT examples (cell, sheet, named range), plus the R1C1 variant and notes, and 4 challenges with answers. No sign-up required.

Frequently asked questions

What does INDIRECT do in Excel?

INDIRECT turns a text string into a live cell or range reference, e.g. =INDIRECT("B"&D2) references cell B plus the row number in D2. It lets formulas build references dynamically.

How do I reference a sheet chosen by a cell?

Concatenate the sheet name: =INDIRECT(D1 & "!A1") returns A1 from whatever sheet D1 names.

Why is INDIRECT slow or returning #REF!?

INDIRECT is volatile, so many of them recalc constantly. #REF! means the built text isn't a valid reference, or it points at a closed workbook, which INDIRECT can't read.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: INDIRECT · ADDRESS