INDIRECT Excel function

The INDIRECT function, as the name suggests, can be used to: indirect cell references in a worksheet formula. This is done by entering a cell reference in the cell read by the function.

Remark † This guide covers Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, and Excel Online.

Contents
  1. Find data with the INDIRECT function
  2. INCORRECT syntax and function arguments
  3. #ref! Errors and IMMEDIATE
  4. Enter an IMMEDIATE function

Find data with the INDIRECT function

As shown in the example above, the INDIRECT function in cell D2 returns the data that is in cell B2 (the number 27), even if it does not contain a direct reference to this cell.

Here’s how it goes:

  1. The INDIRECT function is located in cell D2.

  2. The cell reference in parentheses tells the function to read the contents of cell A2, which contains the 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 the OFFSET and SUM functions shown in line 7 of the above example, to create more complex formulas. For this to work, the second function must take a cell reference as its argument.

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

INCORRECT syntax and function arguments

Function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments.

The syntax for the INDIRECT function is:

= INDIRECT(Ref_text, A1)

Ref_text (necessary). A valid cell reference (can be an A1 or R1C1 style reference) or named range. See line 6 in the image above where cell A6 is named alpha.

A1 (not necessary). A Boolean value (TRUE or FALSE only) that specifies which cell reference style is included in the Ref_text argument.

  • If A1 is TRUE or omitted, the function interprets Ref_text as the more common A1-style cell reference, as in line 3 of the above example.

  • If A1 is FALSE, the function interprets Ref_text as the less common R1C1-style cell reference.

#ref! Errors and IMMEDIATE

INDIRECT will return #REF! error value as function argument Ref_text

  • Not a valid cell reference (see line 8 above).

  • Contains an external link to another workbook and this workbook is not open.

  • Points to a range of cells outside the worksheet (outside row 1,048,576 or column XFD).

Enter an IMMEDIATE function

You can manually enter the entire formula into a worksheet cell, for example:

= INDIRECT(A2)

Entering manually in Excel Online is the best option because the online version of Excel doesn’t have as many features as the desktop versions.

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

  1. Select cell D2 to make it active.

  2. Select formulas

  3. Select Search and help to open the drop-down list of functions.

  4. Select STRAIGHT AWAY in the list to open the function’s dialog box.

  5. In the dialog box, select the line Ref_text

  6. Select cell A2 on a sheet to enter a cell reference as an argument in the dialog box Ref_text

  7. Select Okay to end the function and close the dialog box.

Number 27 appears in cell D2 because that is the data that is in cell B2.

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

Leave a Reply

Your email address will not be published.