How to use INDIRECT Function in Excel

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.

Add a Comment

Your email address will not be published. Required fields are marked *