INDIRECT - Microsoft Office Excel

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax

INDIRECT(ref_text,a1)

Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.
Note This behavior is different from Excel versions earlier than Microsoft Office Excel, which ignore the exceeded limit and return a value.

A1 is a logical value that specifies what type of reference is contained in the cell ref_text.

If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2
3
4
5

A	B
Data	Data
B2	1.333
B3	45
George	10
5	62
Formula	Description (Result)
=INDIRECT($A$2)	Value of the reference in cell A2 (1.333)
=INDIRECT($A$3)	Value of the reference in cell A3 (45)
=INDIRECT($A$4)	If the cell B4 has the defined name "George," the value of the defined name is returned (10)
=INDIRECT("B"&$A$5)	Value of a reference in cell A5 (62)

When you create a formula that refers to a cell, the reference to the cell will be updated if: (1) the cell is moved by using the Cut command to delete the cell or (2) the cell is moved because rows or columns are inserted or deleted. If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax:

=INDIRECT("A10")

How to copy an example

See also: