INDIRECT function in Excel
Understand how the INDIRECT function in Excel changes text into a real cell reference. If you want to connect text together, you can use the ‘&’ operator.
💎 Cell Reference
In this section, we will discuss how to use the INDIRECT function for cell references.
1. As an example, see the INDIRECT function below.
Explanation: =INDIRECT(A1) becomes =INDIRECT(“D1”). The text “D1” turns into a real cell reference. In short, =INDIRECT(“D1”) functions the same way as =D1.
2. Another INDIRECT function application works in the same way.
3. Do we really need the INDIRECT function? Yes. This shows what happens when the INDIRECT function is not used.
4. Combine the string “D” with cell A1 using the & operator.
Explanation: The formula above reduces to =INDIRECT(“D1”). Again, =INDIRECT(“D1”) reduces to =D1.
💎 Range Reference
The INDIRECT function in Excel helps you to change a text into a real range reference. For example, use SUM and INDIRECT.
Explanation: the formula above reduces to =SUM(INDIRECT(“D3:D6”)). INDIRECT converts text like “D3:D6” into a range reference. In other words, =SUM(INDIRECT(“D3:D6”)) reduces to =SUM(D3:D6).
💎 Named Range
Use INDIRECT in Excel to make a text work as a named range.
1. Here, the AVERAGE function uses a named range, called ‘Scores’.
Explanation: The named range “Scores” points to cells D1 to D3.
2. Here, the AVERAGE function indicates an error.
Explanation: The function =AVERAGE(“Scores”) returns an error because it expects numbers instead of text.
3. The INDIRECT function below does the trick.
Explanation: =AVERAGE(INDIRECT(“Scores”)) reduces to =AVERAGE(Scores).
💎 Worksheet Reference
In Excel, the INDIRECT function allows you to create a worksheet reference that updates automatically.
1. Example of using a worksheet reference.
Note: Cell A1 in Sheet1 has the value 10. Cell A1 on Sheet2 contains the value 20. Cell A1 on Sheet3 contains the value 30.
2. On the Summary sheet, type the INDIRECT function as shown below. To join A1 with “!A1”, you can use the & operator.
Explanation: The formula above reduces to =INDIRECT(“Sheet1!A1”). Using the INDIRECT function, the text “Sheet1!A1” transforms into a valid reference. In other words, =INDIRECT(“Sheet1!A1”) reduces to =Sheet1!A1.
3. If a sheet name has spaces or special characters, put it in single quotes and use the INDIRECT function like this.
10/15 Completed! Learn more about lookup & reference ➝
Next Chapter: Financial Functions