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

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

  1. An address built from a row number

    ABCDEF
    1MonthSalesRow to read
    2Jan1003150
    3Feb150
    4Mar90

    In F2: =INDIRECT("B" & D2) → 150

    "B" & D2 makes the text B3, and INDIRECT reads that cell.

    Try this example

  2. Add up a range that is typed in a cell

    ABCDEF
    1MonthSalesRange
    2Jan100B2:B3250
    3Feb150
    4Mar90

    In F2: =SUM(INDIRECT(D2)) → 250

    Change D2 to B2:B4 and the total becomes 340, with no change to the formula.

    Try this example

  3. A range that inserting rows cannot move

    ABCD
    1MonthSales
    2Jan100460
    3Feb150
    4Mar90
    5Apr120

    In D2: =SUM(INDIRECT("B2:B5")) → 460

    Try this example

Common mistakes

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.

Translate a whole formula

Try INDIRECT in the playground

Related functions