Excel INDIRECT Function

The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.

INDIRECT Function Arguments

The INDIRECT function has two arguments:

1. ref_text: A cell reference or text string (or both), that create the range reference. The referenced range can be a cell, a range of cells, or a named range.
2. a1: TRUE or FALSE. Does the reference use A1 reference style? If this argument is TRUE, or omitted, the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1 style.

 

Note: If the INDIRECT formula refers to a different workbook, that workbook must be open, or the formula will return a #REF! error.

Lock a Cell Reference

If you create a simple link to a cell, e.g. =A4, then insert a row above row 4, your formula will automatically change to =A5. To prevent this change, you can use the INDIRECT function.

To demonstrate the difference, you'll create two formulas -- one that uses a simple link, and one that uses a text string with an INDIRECT function.

1. In cell A4, type a number (10 in this example).
2. In cell B2, type the following formula:
      =INDIRECT("A4")
3. Press the Enter key, and the formula will return the number in cell A4.
4. In cell C2, type the following formula:
      =A4
5. Press the Enter key, and the formula will return the number in cell A4. 
6. To insert a row, right-click on the Row 4 button, and from the pop-up menu, choose Insert.
7. As shown below, cell B2, which uses the INDIRECT function, now returns 0, because cell A4 is empty. The text string, "A4", in the INDIRECT formula did not change when the row was inserted.
8. Cell C2, which contains the link, still returns 10, because its formula has changed to =A5. 

 

Create a Reference from a Cell Value

To create the ref_text argument, you can also refer to a cell, and use its contents. This makes the formula more flexible, as its results will change, if the cell contents change.

In this example, you'll create formulas with the INDIRECT function, to compare the A1 and R1C1 reference styles.

1. In cells A1:A5, type a set of numbers, as shown below.

2. In cell C2, type:  A4
3. In cell C2, type:  R4C1
4. In cell D2, type the formula: =INDIRECT(C2)
5. Press the Enter key, and the formula returns the number in cell A4
6. In cell D3, type the formula: =INDIRECT(C3,FALSE)
7. The FALSE in the second argument indicates that the R1C1 reference style will be used.
8. Press the Enter key, and the formula returns the number in cell A4