Lesson 162: How to use the INDIRECT function in Excel

Using the INDIRECT function in Excel to convert a text string to a valid reference. You can use the & operator to create text strings.

How to use the INDIRECT function in Excel

1. For example, see the INDIRECT function below.

Explanation: =INDIRECT(A1) becomes =INDIRECT(“D1”). The INDIRECT function converts the text string “D1” into a valid cell reference. In other words, =INDIRECT(“D1”) reduces to =D1.

2. The simple INDIRECT function below gives the same result.

3. If the INDIRECT function is not used, this will be the result.

4. Use the & operator to concatenate the string “D” with the value in cell A1.

Scope reference

Use the INDIRECT function in Excel to convert a text string into a valid range reference. For example, use SUM and INDIRECT.

Explanation: the above formula reduces to =SUM(INDIRECT(“D3:D6”)). The INDIRECT function converts the text string “D3:D6” into a valid range reference. In other words, =SUM(INDIRECT(“D3:D6”)) reduces to =SUM(D3:D6).

Area name

Use the INDIRECT function in Excel to convert a text string into a valid named range.

1. For example, the AVERAGE function below uses the Scores region name.

Explanation: the Scores region name refers to the range D1:D3.

2. However, the AVERAGE function below returns an error.

Explanation: =AVERAGE(“Scores”) returns an error because Excel cannot calculate the average of a text string.

3. The INDIRECT function below will fix that.

Explanation: =AVERAGE(INDIRECT(“Scores”)) reduces to =AVERAGE(Scores).

Spreadsheet Reference

1. This is a simple sheet reference.

Note: cell A1 on Sheet1 contains the value 10. Cell A1 on Sheet2 contains the value 20. Cell A1 on Sheet3 contains the value 30.

Explanation: the above formula reduces to =INDIRECT(“Sheet1!A1”). The INDIRECT function converts the text string “Sheet1! A1” to a valid sheet reference. In other words, =INDIRECT(“Sheet1!A1”) reduces to =Sheet1!A1.

3. If your sheet name contains spaces or other special characters, enclose the sheet name in quotes. Modify the INDIRECT function as shown below.

In addition, you can also view many other excel articles here.