Excel INDIRECT Function

How to use Excel's INDIRECT function

 rawpixel | Unsplash

The INDIRECT function, as its name suggests, can be used to indirectly reference a cell in a worksheet formula. This is done by entering a cell reference into the cell that is being read by the function.

Note This tutorial covers Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, and Excel Online.

Find Data with the INDIRECT Function

Reference Data in Other Cells with Excel's INDIRECT Function

As shown in the example above, the INDIRECT function in cell D2 displays the data located in cell B2 (the number 27) even though it contains no direct reference to that cell.

Here's how this happens:

  1. The INDIRECT function is located in cell D2.
  2. The cell reference contained in the round brackets tells the function to read the contents of cell A2, which contains a cell reference to cell B2.
  3. The function then reads the contents of cell B2, where it finds the number 27.
  4. The function displays this number in cell D2.

INDIRECT is often combined with other functions, such as the OFFSET and SUM functions shown in row 7 of the example above, to create more complex formulas. For this to work, the second function must accept a cell reference as an argument.

A common use for INDIRECT is to change one or more cell references in a formula without editing the formula itself.

The INDIRECT Function Syntax and Arguments

 

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the INDIRECT function is:

= INDIRECT(Ref_text, A1)

Ref_text  (required). A valid cell reference (can be either A1 or R1C1 style reference) or a named range. See row 6 in the image above where cell A6 has been given the name Alpha.

A1 (optional). A logical value (TRUE or FALSE only) that specifies what style of cell reference is contained in the Ref_text argument.

  • If A1 is TRUE or omitted, Ref_text is interpreted by the function as using the more common A1-style cell reference as used in row 3 of the example above.
  • If A1 is FALSE, Ref_text is interpreted by the function as using the less common R1C1-style cell reference.

#REF! Errors and INDIRECT

INDIRECT will return the #REF! error value if the function's Ref_text argument:

  • Is not a valid cell reference (see row 8 above).
  • Contains an external reference to a different workbook and that workbook is not open.
  • Refers to a cell range outside of the worksheet (beyond row 1,048,576 or column XFD).

Enter the INDIRECT Function

Excel INDIRECT function

It is possible to type the entire formula manually into a worksheet cell,  such as:

=INDIRECT(A2)

Entering it manually in Excel Online is the best option, as the online version of Excel does not have as many features as the desktop versions.

Another option is to use the function's dialog box to enter the function and its arguments into cell D2, as outlined in the steps below.

  1. Select cell D2 to make it the active cell.
  2. Select Formulas.
  3. Select Lookup and Reference to open the function drop-down list.
  4. Choose INDIRECT in the list to bring up the function's dialog box.
  5. In the dialog box, select the Ref_text line.
  6. Select cell A2 in the worksheet to enter the cell reference into the dialog box as the Ref_text argument.
  7. Select OK to complete the function and close the dialog box.

The number ​27 appears in cell D2 since it is the data located in cell B2.

When you select cell D2, the complete function =INDIRECT(A2) appears in the formula bar above the worksheet.