Excel INDIRECT Function

01
of 01

Finding Data with the INDIRECT Function

Reference Data in Other Cells with Excel's INDIRECT Function
Reference Data in Other Cells with Excel's INDIRECT Function. © Ted French

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.

As shown in the example above, the INDIRECT function in cell D2 ends up displaying the data located in cell B2 - the number 27 - even though it contains no direct reference to that cell.

How this happens, in a somewhat convoluted way, is:

  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 another cell reference - 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 OFFSET and SUM - 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 let you change one or more cell references in a formula without having to edit 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 - 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 - 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 - 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.

Entering the INDIRECT Function

Although it is possible to type the entire formula such as

=INDIRECT(A2)

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

  1. Click on cell D2 to make it the active cell;
  2. Click on the Formulas tab of the ribbon menu;
  3. Choose Lookup and Reference from the ribbon to open the function drop down list;
  4. Click on INDIRECT in the list to bring up the function's dialog box
  5. In the dialog box, click on the Ref_text line;
  6. Click on cell A2 in the worksheet to enter the cell reference into the dialog box as the Ref_text argument;
  7. Click OK to complete the function and close the dialog box;
  8. The number 27 appears in cell D2 since it is the data located in cell B2
  9. When you click on cell D2 the complete function =INDIRECT(A2) appears in the formula bar above the worksheet.

 

Format
mla apa chicago
Your Citation
French, Ted. "Excel INDIRECT Function." ThoughtCo, Aug. 3, 2017, thoughtco.com/excel-indirect-function-tutorial-3124099. French, Ted. (2017, August 3). Excel INDIRECT Function. Retrieved from https://www.thoughtco.com/excel-indirect-function-tutorial-3124099 French, Ted. "Excel INDIRECT Function." ThoughtCo. https://www.thoughtco.com/excel-indirect-function-tutorial-3124099 (accessed January 17, 2018).