The Excel INDIRECT Function

Function Description

The Excel Indirect function converts a text string into a cell reference.

If you type the reference B1 in an Excel formula, Excel understands that this refers to cell B1. However, Excel does not understand the text string "B1" to be a reference. Therefore, if your cell reference takes the form of a text string, you will need to use the Indirect function to convert this into an actual cell reference.

The syntax of the Indirect function is:

INDIRECT( ref_text, [a1] )

where the arguments are:

ref_text

The text describing the reference.

[a1]

An optional logical argument that defines the style of the ref_text reference.

This can be either:

True - to denote that the reference is in A1 style;
False - to denote that the reference is in R1C1 style.

If the [a1] argument is omitted, it takes on the default value "True".

By default, Excel uses the A1 referencing style. If you want to use the R1C1 referencing style, you will need to set this in your Excel options. To learn more about referencing styles, see the Excel Reference Styles page.

Excel Indirect Function Examples

Column A of the following spreadsheet shows four simple examples of the Excel Indirect function.

Formulas:

	A	C	D	E
1	=INDIRECT( "C1" )	8	9	0
2	=INDIRECT( "D" & 4 )	3	4	5
3	=INDIRECT( "E" & ROW() )	9	1	3
4	=SUM( INDIRECT( "C4:E4" ) )	4	6	2

Results:

	A	C	D	E
1	8	8	9	0
2	6	3	4	5
3	3	9	1	3
4	12	4	6	2

Note that, in the above spreadsheet:

In all of the examples, the A1 referencing style is used and so the [a1] argument can be omitted from the Indirect function.
In cell A3, the Excel ROW function has been used to return the reference to the current row number (i.e. 3), and use this to form part of the cell reference. Therefore, the Indirect formula returns the value from cell E3.
In cell A4, the Indirect function returns a reference to the range C4:E4, and then passes this to the Excel SUM function. The SUM function therefore returns the sum of cells C4, D4 & E4 (i.e. 4 + 6 + 2).

For further details and examples of the Excel Indirect Function, see the Microsoft Office website.

Excel Indirect Function Error

If you get an error from the Indirect function, this is likely to be the #REF! error:

Common Error

#REF!

Occurs if the supplied ref_text is not recognised as a valid cell reference.

Return to the Excel Lookup & Reference Functions Page
Return to the List of All Built-In Excel Functions