The INDIRECT function converts a text string and into a cell reference.

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

Syntax

=INDIRECT(ref_text,[A1])

Arguments

Argument Description
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 reference
  False – to denote that the reference is in R1C1-style reference
  If the [A1] argument is omitted, it takes on the default value “True”

Note: Excel uses the A1 referencing style by default. If you want to use the R1C1 referencing style, you will need to set this in your Excel options.

Examples

  A B C D E F G
1         Formula Result Notes
2 8 9 0   =INDIRECT(“A1”) 8  
3 4 5   =INDIRECT(“B”&5) 6  
4 9 1 3   =INDIRECT(“C”&ROW()) 3 The ROW function is used to reference the current row number, i.e., 4
5 4 6 2   =SUM(INDIRECT(“A5:C5”)) 12 The INDIRECT function returns reference to the range A5:C5. This is supplied to the SUM function which evaluates the sum of 4+6+2=12

Common Function Error(s)

Problem What went wrong
#REF! Occurs if the supplied ref_text cannot be interpreted as a valid cell reference