Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Lookup & reference

INDIRECT

INDIRECT in Dutch Excel

Turns text such as B5 or Sales!B5 into a live reference, so a formula can point wherever a cell tells it to.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: F2 holds the formula
Row	A	B	C	E	F
1	Account	Description	Balance	Cell address	Value there
2	11001100	Bank	€52,480.60€ 52.480,60	C4	-€96,340.25€ -96.340,25=INDIRECT(E2)=INDIRECT(E2)
3	13001300	Trade receivables	€148,920.00€ 148.920,00
4	16001600	Trade payables	-€96,340.25€ -96.340,25
5	18001800	VAT payable	-€18,755.40€ -18.755,40
6	80008000	Revenue	-€912,600.00€ -912.600,00

Result: -€96,340.25€ -96.340,25

=INDIRECT(E2)

=INDIRECT(E2)

What INDIRECT does and when to use it

What it does: it turns text into a reference, for example the text B5 typed in a cell becomes the cell B5 itself, so a formula can read a balance from the address someone types.

When to use it: when the target really changes by name, such as a sheet per month or a table chosen from a dropdown. For a lookup in a fixed block, INDEX with XMATCH does the same without the drawbacks; for a range that grows with the data, use a Table.

How Excel evaluates it

Excel first evaluates ref_text completely, including any & that glues pieces together, so it ends up with one string. It then parses that string as an address in A1 style, or in R1C1 style when a1 is FALSE. If the parse succeeds, the result is a reference, which the surrounding function or the cell then reads as if you had typed the address yourself. If it fails, the result is #REF!.

Because the target is only known after evaluation, Excel cannot record it in the dependency tree: the function is volatile and recalculates on every change anywhere, and Trace Precedents cannot follow it.

Watch out: The text inside INDIRECT does not move with the sheet. When rows are inserted or a sheet is renamed, the text stays as it was while the real cells move.

Mental model

A forwarding address written on an envelope: the post office delivers to whatever is written there, and if the street is renamed the envelope still carries the old name.

Terms used on this page: reference, range, cell

Syntax

Official INDIRECT documentation

INDIRECT(ref_text, [a1])

INDIRECT(ref_text; [a1])

Arguments
Argument	Type	Meaning	Default
`ref_text`	Required	Text that reads like a reference: a cell holding B5, or a piece such as "B" & E2.
`a1`	Optional	TRUE or omitted: text is A1 style (D4). FALSE: text is R1C1 style (R4C4).	`TRUE`

Return value:The reference described by ref_text

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 6 sheets, 18 KB)

Beginner

Show the balance of whichever cell address you type

The client's trial balance sits in A1:C6. While you tick it off against the ledger you want a cell where you type an address such as C4 and see the balance that cell holds, without scrolling through the sheet. A plain reference to the input cell would show the text C4 itself; you need the value behind it.

Sheet excerpt: F2 holds the formula
Row	A	B	C	E	F
1	Account	Description	Balance	Cell address	Value there
2	11001100	Bank	€52,480.60€ 52.480,60	C4	-€96,340.25€ -96.340,25=INDIRECT(E2)=INDIRECT(E2)
3	13001300	Trade receivables	€148,920.00€ 148.920,00
4	16001600	Trade payables	-€96,340.25€ -96.340,25
5	18001800	VAT payable	-€18,755.40€ -18.755,40
6	80008000	Revenue	-€912,600.00€ -912.600,00
7

Formula cell (F2)Input

Formula in F2

=INDIRECT(E2)

=INDIRECT(E2)

Result: -€96,340.25€ -96.340,25

Step by step

Excel reads E2 and finds the text C4. To Excel this is two characters, no different from the word 'cat'.
INDIRECT tries to read that text as an address. C4 is a valid A1-style address: column C, row 4. The parse succeeds and INDIRECT returns a reference to C4.
The cell F2 now behaves as if it contained =C4, so it shows what C4 holds: -96340.25, the credit balance of account 1600, trade payables.
Compare with =E2 in F2: that would show the text C4, because a reference to E2 returns the content of E2. INDIRECT is the step that turns the label into the cell.
Type B5 in E2 and F2 shows VAT payable. Type A2 and it shows 1100. The formula never changes; only the text it reads does.
Type C9 and F2 shows 0, because C9 is empty and an empty cell reads as 0. Type 'balance' and F2 shows #REF!, because that is not an address.

Watch out: A trailing space in E2 ('C4 ') is enough for #REF!. INDIRECT does not trim; wrap the argument in TRIM if the address is typed or pasted from an export.

More on this example: why it works, 1 more pitfall

Why it works

There are two layers here: the text in E2, and the cell that text names. A normal reference stops at the first layer. INDIRECT reads the first layer and returns the second, and everything else in Excel then treats the result as an ordinary reference.

More to watch out for

Insert a row above the table and the balances move down one row, but E2 still says C4, which now holds the receivables balance. Nothing warns you, and you tick off the wrong account.

Intermediate

Year-to-date revenue through a period typed in a cell

To reconcile revenue to the client's specification you need the ledger total of account 8000 from period 1 up to the last closed period, and the closed period moves as the audit progresses. Editing the SUM range by hand each time is exactly the step that gets forgotten. The number of closed periods lives in one cell, and the range follows it.

Sheet excerpt: E2 holds the formula
Row	A	B	D	E
1	Period	Revenue 8000	Periods closed	55
2	11	€210,400.00€ 210.400,00	Year to date	€1,060,200.00€ 1.060.200,00=SUM(INDIRECT("B2:B" & (E1 + 1)))=SUM(INDIRECT("B2:B" & (E1 + 1)))
3	22	€185,300.00€ 185.300,00
4	33	€240,150.00€ 240.150,00
5	44	€198,750.00€ 198.750,00
6	55	€225,600.00€ 225.600,00
7	66	€260,300.00€ 260.300,00
8	77	€232,800.00€ 232.800,00
9	88	€175,900.00€ 175.900,00
10

Formula cell (E2)Input

Formula in E2

=SUM(INDIRECT("B2:B" & (E1 + 1)))

=SUM(INDIRECT("B2:B" & (E1 + 1)))

Result: €1,060,200.00€ 1.060.200,00

Step by step

Excel evaluates the text expression first. E1 is 5, so E1 + 1 is 6: the header sits in row 1, so period 5 is in row 6.
The & glues the pieces: "B2:B" followed by 6 gives the text B2:B6. The number 6 is converted to text on the way.
INDIRECT reads B2:B6 as an A1-style range and returns a reference to those five cells.
SUM receives the reference and adds the values: 210400 + 185300 + 240150 + 198750 + 225600 = 1060200.
When period 6 is closed, change E1 to 6. The text becomes B2:B7 and the total 1060200 + 260300 = 1320500. Without INDIRECT this means opening the formula and retyping the range each time, which is where the mistakes came from.
Clear E1 and the text becomes B2:B1, which Excel reads as B1:B2: the header (text, ignored by SUM) plus period 1, giving 210400. A blank parameter produces a plausible number, not an error.

Watch out: Insert a row above the table and the data shifts to rows 3 to 10, but the text still says B2:B6: the total now covers the header row and four periods and shows 834600 instead of 1060200, with no error. INDIRECT text does not move with the sheet.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

The range is nothing but text until INDIRECT turns it into a reference, so anything that can produce text, including arithmetic on a cell, can decide where the range ends. SUM does not know or care that its range was assembled; it receives an ordinary reference. The +1 encodes the position of the header, which is both what makes it work and what makes it fragile.

More to watch out for

Type 9 in E1 and the range reaches B10, an empty row: the total is all eight periods, 1729200, which is right by accident. Type 20 and it is still 1729200. The parameter has no upper limit unless you add one.

Variations

=SUM(B2:INDEX(B2:B9, E1))

=SUM(B2:INDEX(B2:B9; E1))

The same running total without INDIRECT: INDEX returns a reference to the fifth cell, and B2:INDEX(...) is a range that moves with the sheet and is not volatile. Prefer this.

=SUM(OFFSET(B2, 0, 0, E1, 1))

=SUM(OFFSET(B2; 0; 0; E1; 1))

OFFSET builds a range E1 rows tall starting at B2. Also volatile, but it does follow inserted rows.

Advanced

Sum the postings between two entry numbers

In journal entry testing you test a run of sales entries from a first to a last entry number, for example the entries around the year-end cut-off. Two cells hold the first and the last entry number. The formula has to find the row of each entry, build the range between them and add up the amounts, and it must fail loudly when an entry number is mistyped rather than return a partial total.

Sheet excerpt: E3 holds the formula
Row	A	B	D	E
1	Entry no.	Amount	From entry	25-0412
2	25-0410	€4,120.50€ 4.120,50	To entry	25-0415
3	25-0411	€3,875.00€ 3.875,00	Total of the run	€17,316.00€ 17.316,00=SUM(INDIRECT("B" & (MATCH(E1, A2:A9, 0) + 1) & ":B" & (MATCH(E2, A2:A9, 0) + 1)))=SUM(INDIRECT("B" & (MATCH(E1; A2:A9; 0) + 1) & ":B" & (MATCH(E2; A2:A9; 0) + 1)))
4	25-0412	€4,460.25€ 4.460,25
5	25-0413	€3,990.00€ 3.990,00
6	25-0414	€4,210.75€ 4.210,75
7	25-0415	€4,655.00€ 4.655,00
8	25-0416	€4,380.50€ 4.380,50
9	25-0417	€3,920.00€ 3.920,00
10

Formula cell (E3)Input

Formula in E3

=SUM(INDIRECT("B" & (MATCH(E1, A2:A9, 0) + 1) & ":B" & (MATCH(E2, A2:A9, 0) + 1)))

=SUM(INDIRECT("B" & (MATCH(E1; A2:A9; 0) + 1) & ":B" & (MATCH(E2; A2:A9; 0) + 1)))

Result: €17,316.00€ 17.316,00

Step by step

Excel evaluates the innermost functions first. MATCH(E1, A2:A9, 0) looks for 25-0412 in the entry column with exact matching and finds it in the third cell, so it returns 3. Adding 1 for the header row gives sheet row 4.
The second MATCH looks for 25-0415 and finds it in the sixth cell: 6, plus 1 is row 7.
The & operators assemble the text: "B" & 4 & ":B" & 7 gives B4:B7. At this point it is still text.
INDIRECT reads B4:B7 and returns a reference to those four cells: entries 25-0412 to 25-0415.
SUM adds them: 4460.25 + 3990 + 4210.75 + 4655 = 17316.
Type 25-0421 in E2, an entry that does not exist, and MATCH returns #N/A; the text cannot be built, INDIRECT never runs, and E3 shows #N/A. That is the loud failure you asked for.
Swap the entries (From 25-0415, To 25-0412) and the text is B7:B4, which Excel reads as B4:B7: the same 17316.

Watch out: Insert one row above the table. MATCH still finds 25-0412 at position 3 because its range moved with the sheet, but the text becomes B4:B7 while that entry now sits in B5: the formula silently sums entries 25-0411 to 25-0414, 16536, and looks fine.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

MATCH converts entry numbers into positions, arithmetic converts positions into sheet rows, and text concatenation turns rows into an address that INDIRECT makes real. Each step is independent, which is why a wrong entry number stops the chain with #N/A instead of quietly summing the wrong entries. The weakness is the +1: it hard-codes where the header is, and INDIRECT will not notice when that changes.

More to watch out for

If the entry numbers in column A are real numbers such as 250412 while E1 holds the text 25-0412, or the other way round, MATCH finds nothing. Both sides must be the same type, and an export often turns codes into numbers.
Because INDIRECT is volatile, this cell recalculates on every edit anywhere in the workbook. One such cell costs nothing; a journal test file with 500 of them is noticeably slow.

Variations

=SUM(INDEX(B2:B9, MATCH(E1, A2:A9, 0)):INDEX(B2:B9, MATCH(E2, A2:A9, 0)))

=SUM(INDEX(B2:B9; MATCH(E1; A2:A9; 0)):INDEX(B2:B9; MATCH(E2; A2:A9; 0)))

The same range built from two INDEX references joined by a colon. Not volatile, and it survives inserted rows because every piece is a real reference. This is the construction to prefer.

=SUM(XLOOKUP(E1, A2:A9, B2:B9):XLOOKUP(E2, A2:A9, B2:B9))

=SUM(XLOOKUP(E1; A2:A9; B2:B9):XLOOKUP(E2; A2:A9; B2:B9))

In Excel 2021 or Microsoft 365, XLOOKUP returns a reference too, so two of them joined by a colon form the range in one readable line.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Read the same account from a month sheet chosen by name

The client delivered its trial balance as one sheet per month (Oct, Nov, Dec), all with the same layout: account in column A, description in B, month-end balance in C. On your working paper you type a month in I1 and an account in I2 and want that account's balance from the matching sheet, for example to compare the movement between months.

The sheet name is text in a cell, and Excel has no way to turn such text into a sheet reference except INDIRECT. The data below is the Nov sheet; the cells in columns H and I are on the working paper.

Sheet excerpt: I3 holds the formula
Row	A	B	C	H	I
1	Account	Description	Balance Nov	Month (sheet)	Nov
2	11001100	Bank	€47,310.80€ 47.310,80	Account	13001300
3	13001300	Trade receivables	€151,240.00€ 151.240,00	Balance	€151,240.00€ 151.240,00=LET( prefix, "'" & I1 & "'!", accounts, INDIRECT(prefix & "A2:A6"), balances, INDIRECT(prefix & "C2:C6"), INDEX(balances, XMATCH(I2, accounts)) )=LET( prefix; "'" & I1 & "'!"; accounts; INDIRECT(prefix & "A2:A6"); balances; INDIRECT(prefix & "C2:C6"); INDEX(balances; XMATCH(I2; accounts)) )
4	16001600	Trade payables	-€88,415.50€ -88.415,50
5	18001800	VAT payable	-€21,060.20€ -21.060,20
6	80008000	Revenue	-€845,300.00€ -845.300,00
7

Formula cell (I3)Input

Formula in I3

=LET(
  prefix, "'" & I1 & "'!",
  accounts, INDIRECT(prefix & "A2:A6"),
  balances, INDIRECT(prefix & "C2:C6"),
  INDEX(balances, XMATCH(I2, accounts))
)

=LET(
  prefix; "'" & I1 & "'!";
  accounts; INDIRECT(prefix & "A2:A6");
  balances; INDIRECT(prefix & "C2:C6");
  INDEX(balances; XMATCH(I2; accounts))
)

Result: €151,240.00€ 151.240,00

Step by step

LET defines prefix: an apostrophe, the text of I1 (Nov), an apostrophe and an exclamation mark give the text 'Nov'!. The apostrophes are only required for names with spaces, but they do no harm for Nov.
accounts is INDIRECT("'Nov'!A2:A6"). Excel reads the text as a range on the sheet Nov and returns a reference to its five account numbers: 1100, 1300, 1600, 1800, 8000. balances works the same way for 'Nov'!C2:C6.
XMATCH(I2, accounts) looks for 1300 in that list with exact matching and finds it in the second cell, so it returns 2.
INDEX(balances, 2) returns the second cell of 'Nov'!C2:C6, which is 'Nov'!C3. I3 shows 151240, the receivables balance at the end of November.
Type Dec in I1 and the same formula reads the sheet Dec. If 1300 stands at 162480 there, I3 shows 162480. Type Oct and, with 131050 on that sheet, I3 shows 131050. The formula never changes; only the text in I1 does. The 162480 and 131050 are illustrative balances on the other two sheets.
Type Nv in I1, a sheet that does not exist, and INDIRECT returns #REF!, which passes through XMATCH and INDEX: a mistyped month fails loudly instead of showing another month's balance.

Watch out: A sheet name with a space or a hyphen, such as Nov 2025, needs the apostrophes around it. Without them INDIRECT returns #REF!, while the same sheet works as soon as the apostrophes are in the prefix. Keep the apostrophes in the formula even when the name has none.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

A sheet name is part of the reference syntax, not a value that a function can receive, so INDEX, XMATCH or XLOOKUP cannot pick a sheet from a cell. INDIRECT is the one step that turns text such as 'Nov'!A2:A6 into a real range, and everything after it works with ordinary references. LET builds the prefix once and names the two ranges.

Do not use this construction when you can stack the months into one table with a month column: SUMIFS or XLOOKUP on that table is not volatile, shows its precedents and does not break when a sheet is renamed.

The trade-off is that the table must be built first, and that is not possible when the client delivers a workbook with one sheet per month and you must read it as delivered. INDIRECT also cannot read a workbook that is closed.

More to watch out for

The formula assumes every month sheet has the same layout: accounts in A2:A6 and balances in C2:C6. If December has an extra account in row 4, the text still says A2:A6 and C2:C6, so the last account is left out of the lookup without a warning.
Rename the sheet Nov to November and every cell that reads the old name from I1 shows #REF!, because the text in I1 does not update. Ordinary references follow a rename; text does not, so check the sheet names in I1 against the tabs after any change.

Variations

=INDEX(INDIRECT("'" & I1 & "'!C2:C6"), MATCH(I2, INDIRECT("'" & I1 & "'!A2:A6"), 0))

=INDEX(INDIRECT("'" & I1 & "'!C2:C6"); MATCH(I2; INDIRECT("'" & I1 & "'!A2:A6"); 0))

The same lookup with MATCH instead of XMATCH and without LET, so it works in older Excel versions. It repeats the prefix twice, which is what LET avoided.

=SUMIF(INDIRECT("'" & I1 & "'!A2:A6"), I2, INDIRECT("'" & I1 & "'!C2:C6"))

=SUMIF(INDIRECT("'" & I1 & "'!A2:A6"); I2; INDIRECT("'" & I1 & "'!C2:C6"))

SUMIF adds all lines of the account instead of returning the first. Use it when an account can occur more than once on a month sheet; the answer is 151240 here as well because 1300 occurs once.

Common mistakes

Common mistakes
Symptom	Cause	Fix
#REF! after a sheet was renamed	A sheet called Jan was renamed to January and every INDIRECT that mentioned it now shows #REF!.	Ordinary references update when a sheet is renamed; text does not. Keep sheet names in cells that the formulas read, so one edit fixes them all, and treat sheet renaming as a change that needs testing.
Old cells shown after inserting a row	You inserted a row above the table and the INDIRECT formulas still show the old cells, one row off, with no error.	Fixed text such as `B2:B6` stays where it was. Build row numbers from something that does move, such as ROW(A2) or a MATCH on a header, or replace INDIRECT with INDEX, which follows the data.
The workbook has become slow	A workbook has become slow, and it turns out hundreds of INDIRECT formulas fetch one cell each from a sheet per month.	Volatile functions recalculate on every edit. Consolidate the months into one table with a month column, and replace the INDIRECT formulas with SUMIFS or XLOOKUP on that table.
#REF! on a closed workbook	INDIRECT("[Budget.xlsx]Sheet1!B5") worked yesterday and shows #REF! today.	INDIRECT only reaches other workbooks while they are open; a normal external reference keeps the last value when the file is closed. Use a normal reference, or open the source workbook first.