Excel functions / Lookup
INDIRECT function
Turns text such as "B2" into a real reference to that cell or range.
Syntax
=INDIRECT(ref_text, [a1])
Arguments
ref_text: A cell address or range written as text, or a cell that holds one.a1(optional): TRUE or left out: the text is in A1 style. R1C1 style is not supported here.
When to use it
INDIRECT takes a cell address written as text, such as "B3", and gives back what is in that cell. Because the address is text, a formula can build it: join a column letter to a row number from another cell, or read a whole range from a cell where someone typed it.
It is also the way to make a reference that never changes. Inserting or deleting rows adjusts a normal reference; text stays as it was written.
Examples
An address built from a row number
A B C D E F 1 Month Sales Row to read 2 Jan 100 3 150 3 Feb 150 4 Mar 90 In F2:
=INDIRECT("B" & D2)→ 150"B" & D2makes the text B3, and INDIRECT reads that cell.Add up a range that is typed in a cell
A B C D E F 1 Month Sales Range 2 Jan 100 B2:B3 250 3 Feb 150 4 Mar 90 In F2:
=SUM(INDIRECT(D2))→ 250Change D2 to B2:B4 and the total becomes 340, with no change to the formula.
A range that inserting rows cannot move
A B C D 1 Month Sales 2 Jan 100 460 3 Feb 150 4 Mar 90 5 Apr 120 In D2:
=SUM(INDIRECT("B2:B5"))→ 460
Common mistakes
- Text that is not an address. If the text is not a valid cell or range, such as
"B"on its own or a misspelt name, INDIRECT returns #REF!. - Using it where a normal reference would do. INDIRECT recalculates every time anything on the sheet changes, and Excel cannot trace through it when you audit a formula. Use it only when the address really has to come from text.
- Pointing at a closed workbook. In Excel, INDIRECT can only read a workbook that is open. A reference to a closed file gives #REF!.
Common questions
What does INDIRECT do in Excel?
It turns text into a reference. =INDIRECT("B3") returns the value of cell B3, and =INDIRECT("B" & D2) reads whichever row of column B the number in D2 names.
Why does INDIRECT return #REF!?
The text is not a valid address, it names a sheet or range that does not exist, or it points at a workbook that is closed. Put the text in a cell on its own first to see exactly what INDIRECT is being given.
INDIRECT in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: NEPŘÍMÝ.ODKAZ
- Danish: INDIREKTE
- Dutch: INDIRECT
- Finnish: EPÄSUORA
- French: INDIRECT
- German: INDIREKT
- Hungarian: INDIREKT
- Italian: INDIRETTO
- Norwegian: INDIREKTE
- Polish: ADR.POŚR
- Portuguese (Brazil): INDIRETO
- Portuguese (Portugal): INDIRETO
- Russian: ДВССЫЛ
- Spanish: INDIRECTO
- Swedish: INDIREKT
- Turkish: DOLAYLI
Try INDIRECT in the playground
Related functions
- OFFSET: A range a set number of rows and columns away from a starting cell, of any size.
- ADDRESS: Builds a cell address as text from a row number and a column number.
- INDEX: The value at a given row and column of a range.
- ROW: The row number of a reference (or of the current cell).
- CHOOSE: Picks a value from a list by its position.