The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference
Syntax:= INDIRECT (ref_text, [a1])
The INDIRECT function syntax has the following arguments:
- 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. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.
- If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.Note External references are not supported in Excel Web App.
- If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.Note This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value.
- A1 Optional. A logical value that specifies what type of reference is contained in the cell ref_text.
- If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
-
If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.
Example: Let’s look at some Excel INDIRECT function examples and explore how to use the INDIRECT function as a worksheet function in Microsoft Excel:
Syntax: =INDIRECT(A3)
Result:
Based on the Excel spreadsheet above, the following INDIRECT examples would return:
Syntax: =INDIRECT(A4)
Result: 1400
Syntax: =INDIRECT(A2)
Result: 50
Syntax: =INDIRECT(“A1”)
Result: 100
Syntax: =INDIRECT(“B” & A5)
Result: 500
Syntax: =INDIRECT(“B” & A6)
Result: 700
Syntax: =INDIRECT(“B”&ROW())
Result: 800
Syntax: =INDIRECT(B8&”!A1″)
Result: 100
Syntax: =INDIRECT(B9&”!A1″)
Result: 200
Note:
- References created by INDIRECT are evaluated in real time and the content of the reference is displayed.
- When ref_text is an external reference to another workbook, the workbook must be open.
- a1 is optional. When omitted, a1 is TRUE = A1 style reference.
- When a1 is set to FALSE, INDIRECT will created a an R1C1-style reference.