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

Use the embedded workbook shown here to work with examples of this function. You can inspect and change existing formulas, enter your own formulas, and read further information about how the function works.

To work in-depth with this workbook, you can download it to your computer and open it in Excel. For more information, see the article Download an embedded workbook from OneDrive and open it on your computer.

 
 
Applies to:
Excel 2010, Excel Web App, SharePoint Online for enterprises, SharePoint Online for professionals and small businesses