Skip to content

Excel function · Lookup & reference

Excel INDIRECT function

INDIRECT turns an address written as text, such as "B5" or "B2:B7", into a real reference and returns what is there. It helps when part of the address comes from another cell.

In Turkish Excel: DOLAYLI

What it does

In a normal formula, =B5 is fixed inside the formula. INDIRECT takes the address as text, and you can build that text piece by piece with &: =INDIRECT("B"&E2) reads B5 when E2 holds 5. That way a value in another cell decides which cell is read.

The second argument is the address style: TRUE or omitted expects A1 style like "B5", FALSE expects R1C1 style with row and column numbers. ADDRESS, which builds an address from a row and column number, is often used together with INDIRECT.

Like OFFSET, INDIRECT is volatile and recalculates on every change. Because the address is text, it does not shift when rows or columns are inserted; sometimes that is what you want, sometimes it is a surprise. To simply return the value at a position, INDEX is sturdier.

Syntax

English Excel=INDIRECT(ref_text, [a1])
Turkish Excel=DOLAYLI(başv_metni; [a1])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
ref_textRequiredThe address as text, or an expression that builds it: "B5", "B2:B"&D2, or a cell that contains an address.
a1OptionalTRUE or omitted: A1 style ("B5"). FALSE: R1C1 style (row 5, column 2).

Examples

1Build a range from a number in a cell

The months start on row 2. Adding 1 to the number of months in D2 gives the last row, which is appended to the text "B2:B"; INDIRECT turns that text into a range and SUM adds it.

E2fx=SUM(INDIRECT("B2:B"&(D2+1)))
Build a range from a number in a cell: example table
ABCDE
1MonthCostsMonthsTotal
2Jan48,2003146,850
3Feb51,750
4Mar46,900
5Apr53,300
6May49,850

D2 says 3, so the text is "B2:B4" and the January–March costs are added: 146,850. The header is on row 1, which is why 1 is added to the number of months.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Read a value from a row and column number

F2 holds a row number and G2 a column number. ADDRESS turns them into an address text (H2); INDIRECT turns that text into a reference and returns the value in the cell (I2).

H2fx=ADDRESS(F2,G2)
Read a value from a row and column number: example table
ABCDEFGHI
1BranchJanFebMarRowColumnAddressValue
2Camden412,000398,500436,20034$D$3365,900
3Leeds356,800371,200365,900
4Bristol298,400305,100322,750
  • H2=ADDRESS(F2,G2)
  • I2=INDIRECT(H2)

Row 3, column 4 gives the address $D$3; INDIRECT returns the value there, Leeds's March sales: 365,900.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#REF!

Cause: The text is not a valid address: for example the column letter and row number were never joined, the cell holding the address is empty, or the sheet it names does not exist.

Fix: Build the address text in a separate cell first and look at it; make sure it is a complete address such as "B5".

Reference does not move

Cause: After inserting a row above, INDIRECT("B5") still reads B5 although the data moved to B6.

Fix: If you do not want this, use a normal reference or INDEX instead of fixed text.

Slow workbook

Cause: Many INDIRECT formulas recalculate on every change.

Fix: Prefer INDEX where it can do the same job.

Good to know

  • Mind the quotes: INDIRECT("B5") reads B5, while INDIRECT(B5) reads the address written in B5.
  • When you first build the address text, write the formula without INDIRECT and look at the result (="B2:B"&(D2+1)); once the text is right, wrap it in INDIRECT.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Next to the monthly cost table, add a summary cell that totals the costs up to the number of months I type

Convert to table

Bring your table into the tool and type this. Nexell builds the total with real Excel formulas and shows it in the preview; in the downloaded file, the total changes when you change the number of months.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I get data from another sheet with INDIRECT?

Add the sheet name and an exclamation mark to the address text: =INDIRECT("'"&A2&"'!B5") reads cell B5 of the sheet named in A2. The single quotes are needed when the sheet name contains spaces; if the sheet is renamed or deleted, you get #REF!.

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free