Excel INDIRECT Function: Definition and Uses

The Excel INDIRECT function converts a text reference into a reference that can be used in formulas. This type of reference function is used to apply automated formatting when data ranges change in lookup functions and similar formulas.

Thumbnail introducing the Excel INDIRECT function.

CONTENTS

Excel INDIRECT Function Definition and Syntax

Let’s look at the definition and syntax of the INDIRECT function, along with additional explanations of its syntax and cell reference styles.

Function Definition

The INDIRECT function converts a “cell reference in text form” into a “reference” that can be used in a formula.

The Excel INDIRECT function returns a valid cell reference from a specified text string. INDIRECT is useful when you want to combine text values that can be used as a valid reference.

Function Syntax

The syntax for the Excel INDIRECT function is as follows.

=INDIRECT ( ref_text, [a1] )

Here is an additional explanation of each argument in the Excel INDIRECT function syntax.

ref_text: The text value to convert into a cell reference, range reference, table reference, or worksheet reference.
[a1]: Specifies the reference style used to return a reference. If you omit this argument in the INDIRECT function or enter TRUE, Excel uses the familiar A1 reference style, where columns are letters and rows are numbers. If you enter FALSE, the INDIRECT function uses the R1C1 reference style, where both columns and rows are represented by numbers.

The Difference Between A1 and R1C1 Cell Reference Styles in Excel

The A1 style is the standard format for Excel cell references, where the location within the worksheet is explicitly specified. In contrast, the numeric R1C1 style is useful when working with ranges that move within a worksheet, because it can specify cell positions that change along with the moving range.

Because Excel uses the A1 reference style by default, you can switch to R1C1 by going to Formulas in Excel Options and selecting the R1C1 reference style.

Detailed examples of the R1C1 reference style will be provided when discussing the Excel OFFSET and ADDRESS functions.

Excel INDIRECT Function Syntax Example

Here is an example that best explains the definition of the Excel INDIRECT function.

Explaining the difference between a standard cell reference formula and a formula using the Excel INDIRECT function.

The image above shows the main difference between a standard Excel cell reference and using the INDIRECT function. Cell B2 contains the text B3.

E2 cell = B2

The value returned by the formula above is, of course, the text B3 entered in B2.

E3 cell = INDIRECT(B2)

To use the text value B3 in cell B2 as a valid reference, the INDIRECT function returns the value “EXCELJUMP” from cell B3.

Conclusion

We covered the basic definition and syntax of the Excel INDIRECT function. Because it can convert references, it works well with formulas and lookup functions. Personally, the function I find most useful to use with INDIRECT is the INDEX MATCH function.