Practice free

INDIRECT function

INDIRECT turns text into a live cell reference, so you can build sheet or cell addresses on the fly, for example a dropdown that switches sheets. It is volatile and can be slow in big workbooks.

Example

Read it as a sentence: treat the text Items!D2 as a cell address and give me the value in that cell, which is 12.

Practice this in the app

Syntax

INDIRECT(ref_text, [a1])

Arguments in [square brackets] are optional.

ref_text (required)
Text that describes a reference, such as "B5" or "Sheet2!A1".
a1 (optional)
TRUE or omitted for A1 style, FALSE for R1C1.

Returns: The reference named by the text.

INDIRECT is volatile: Excel works it out again every time anything in the workbook changes.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=INDIRECT("Items!D2") 12 A1 text
=INDIRECT("Data!R2C3",FALSE) 0 R1C1 text
=INDIRECT("data!c2") 0 Lower case
=INDIRECT("'Data'!C2") 0 Quoted sheet
=SUM(INDIRECT("Items!D2:D7")) 68 Range text
=INDIRECT("Data!"&"F1") 0 Blank target
=INDIRECT("abc") #REF! Not a reference
=INDIRECT("") #REF! Empty text

The Items sheet these formulas read

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Volatile and slow in big workbooks.
  • Breaks if you rename the sheet or the text is wrong, since Excel will not update it.
  • Cannot read closed workbooks.

Where it works

Excel
INDIRECT is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .