INDIRECT function

This article describes the formula syntax and usage of the INDIRECT function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax

INDIRECT(ref_text, [a1])

The INDIRECT function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

  • 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

The workbook below shows examples of this function. Inspect them, change existing formulas, or enter your own formulas to learn how the function works.

To work more in-depth with the example data in Excel, download an embedded workbook, and then open it in Excel.

 
 
Applies to:
Excel 2013, Excel Online