Evaluate and return the contents of a reference specified by a text string
=INDIRECT(ref_text, [a1])
INDIRECT defines 'the cell reference to evaluate' and 'the type of reference to evaluate'.
INDIRECT refers to a cell reference in a text string format, evaluates that cell reference and returns the value from that cell reference.
For example, cell F3 contains the text string C5. Cell C5 contains the value 1234. =INDIRECT(F3) evaluates cell F3, refers to cell C5 and returns 1234.
INDIRECT
In the examples below, the INDIRECT function is used to retrieve population data from indirect cell references.
The ref_text can be an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string.
The ref_text can be assembled using the CONCAT function and arrays can be summed using a combination of the INDIRECT and SUM functions.
Use the INDIRECT function to evaluate and return the contents of a reference specified by a text string.
Syntax
=INDIRECT(ref_text, [a1])
The INDIRECT function consists of at least 1 argument:
- ref_text. Required. 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.
- a1. Optional. The type of reference associated with ref_text.
- a1 = TRUE (or omitted). ref_text interpreted as an A1-style reference (cell reference is written as "A1").
- a1 = FALSE. ref_text interpreted as an R1C1-style reference (cell reference is written as "R1C1" which equates to row 1, column 1 which is the same as "A1").
Try it now!
- Enter your formulas into the grey cells.
Scope
INDIRECT defines 'the cell reference to evaluate' and 'the type of reference to evaluate'.
|
=INDIRECT(F3) || where cell F3 contains the cell reference C4, result is the contents of the cell C4. |
|
=INDIRECT(CONCAT("C"&ROW(C6))) || result is the contents of the cell C6, constructed from a concatenation of the letter C and the number 6. |
|
=INDIRECT("C8:C9") || result is a 2 row array of values from the cell range C8:C9. |
|
=SUM(INDIRECT("xl_population")) || result is the sum of values from the named range "xl_population". |
Caveats
- INDIRECT is a volatile function that is recalculated every time Excel recalculates. Use with caution in large or complex workbooks.
- INDIRECT can be used with sheet names. Sheet names with punctuation or spaces must be enclosed with single quotes (').
- INDIRECT can be used to create fixed references that will not change even if rows or columns are inserted or deleted.
- INDIRECT will work with A1-style references, R1C1-style references, a name defined as a reference, or a reference to a cell as a text string.
Common issues
- #REF!: ref_text is not a valid cell reference.
- #REF!: an external workbook containing ref_text is not open.
- #REF!: ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD).
Functions Category