Exceljet

Quick, clean, and to the point

Excel INDIRECT Function

Excel INDIRECT function
Summary 

The Excel INDIRECT function returns a valid reference from a given text string. INDIRECT is useful when you want to convert a text value into a valid cell reference.

Purpose 
Create a reference from text
Return value 
A valid worksheet reference.
Syntax 
=INDIRECT (ref_text, [a1])
Arguments 
  • ref_text - A reference supplied as text.
  • a1 - [optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.
Version 
Usage notes 

The INDIRECT function returns a valid reference from a given text string. INDIRECT is useful when you want to convert a text value into a valid cell reference. For example, to create a reference to cell A1 from text, you can use INDIRECT like this:

=INDIRECT("A1") // returns reference to A1

The purpose of INDIRECT may at first seem baffling (i.e. why use text when you can just provide a proper reference?) but there are many situations where the ability to create a reference from text is useful, including:

  • A formula that needs a variable sheet name
  • A fixed reference that will not change even when rows or columns are deleted
  • Creating numeric arrays with the ROW function in complex formulas

Note: INDIRECT is a volatile function and can cause performance problems in large or complex worksheets.

Variable worksheet name

In the example shown above, INDIRECT is set up to use a variable sheet name like this:

=INDIRECT(B6&"!A1") // sheet name in B6 is variable

The formula in B6, copied down, returns the value in cell A1 in the first 5 sheets using the sheet names entered in column B. In other words, when a different sheet name is entered in column B, a new result is returned. With the same approach, you could allow a user to select a sheet name with a dropdown list, then construct a reference to the selected sheet with INDIRECT.

Fixed reference

The reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. For example, the formula below will always refer to the the first 100 rows of column A, even if rows in that range are deleted or inserted:

=INDIRECT("A1:A100") // will not change

Generate numeric array

A more advanced use of INDIRECT is to create a numeric array with the ROW function like this:

ROW(INDIRECT("1:10")) // create {1;2;3;4;5;6;7;8;9;10}

One use case is explained in this formula, which sums the bottom n values in a range.

You may also run into this idea in more complex formulas that need to assemble a numeric array "on-the-fly". One example is this formula, designed to strip numeric characters from a string.

Notes

  • References created by INDIRECT are evaluated in real time and the content of the reference is displayed.
  • When ref_text is an external reference to another workbook, the workbook must be open.
  • a1 is optional. When omitted, a1 is TRUE = A1 style reference.
  • When a1 is set to FALSE, INDIRECT will created a an R1C1-style reference.
  • INDIRECT is a volatile function, and can cause performance issues.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.