The Excel INDIRECT Function

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

Excel does not understand the text string "B1" to mean a reference to the cell B1. Therefore, if you extract or build up a reference to a cell or range using text, you will need to use the Indirect function to convert this into a reference that Excel can understand.

The format of the 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"

Excel Reference Styles

Excel understands two different styles of referencing for cells and ranges. These are described in the table below :

A1-Style References

The A1-style is the most common form of Excel referencing, and is therefore the default style.

This style of referencing is made up of a letter and a number, which represent the column reference and the row number, respectively.



R1C1-Style References

The R1C1-style of referencing is made up the letter R followed by the row number and the letter C followed by the column number.

The row or column number of the R1C1 style reference can be encased in square brackets, if you want the row or column number to be measured out from the current cell location.

If the row or column number is omitted, this tells Excel to look in the current row or column

Eg. If the current cell is cell C5, then :

R1C1 - refers to cell A1
R[1]C[1] - refers to cell D6
RC[-2] - refers to cell A5
R[1]C1 - refers to cell A6

Note that you need to tell Excel which style of referencing you are using. This is set by checking, or unchecking, the option 'R1C1 referencing style' in the Excel options menu (found in Tools->Options->General in Excel 2003 or MSOffice Button->Excel Options->Formulas in Excel 2007).

By default, Excel uses the A1 style of referencing, and the columns of your spreadsheet are labelled with letters. However, if you select the R1C1 option, you will notice that the top of your Worksheet columns display numbers, instead of letters.

INDIRECT Function Examples

The following spreadsheets show simple examples of the Excel INDIRECT function. The format of the function is shown in the spreadsheet on the left and the results are shown in the spreadsheet on the right.

  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( "C1:E1" ) )   4 6 2
  A B C D E
1 8   8 9 0
2 6   3 4 5
3 3   9 1 3
4 17   4 6 2

Note that, in the above examples :

Return to the Excel Lookup & Reference Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014