The Excel INDIRECT Function

Basic Description

The Excel Indirect function converts a text string and 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 a reference.

The format of the Indirect function is :

INDIRECT( ref_text, a1 )

where the arguments are as follows:

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 this 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.

Indirect Function Examples

The following spreadsheets show simple examples of the Excel Indirect function.

  A B 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
  A B 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 examples :

Further information and examples of the Excel Indirect Function can be found on the Microsoft Office website.

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 cannot be interpreted as a valid cell reference.