Lookup and Reference - INDIRECT Function

Description

The INDIRECT function returns the reference specified by a text string.

If you type the reference B1 in an Excel Formula, Excel understands that this refers to cell B1. However, Excel does not understand the text string "B1" to be a reference. Hence, if your cell reference takes the form of a text string, you will need to use the INDIRECT function to convert this into an actual cell reference.

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])

Arguments

Argument Description Required/ Optional
Ref_text

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.

See Notes below.

 Required
A1

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 A1style reference.

If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

 Optional

Notes

  • 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

  • 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

Applicability

Excel 2007, Excel 2010, Excel 2013, Excel 2016

Example

Indirect Function
advanced_excel_lookup_reference_functions.htm
Advertisements