Excel INDIRECT Function is a useful tool for referencing and retrieving data from different cells or ranges within a worksheet. It allows you to create dynamic references by using text values or cell references.
Syntax
The syntax of the INDIRECT function is as follows:
=INDIRECT(ref_text, [a1])
Arguments
| ref_text | The reference you want to create, either as a text string or a cell reference. |
| [a1] | (Optional) If set to TRUE or omitted, ref_text is interpreted as an A1-style reference. If set to FALSE, ref_text is treated as an R1C1-style reference. |
How to use
You can use the INDIRECT function in various ways to create dynamic references. Here are some examples:
Example 1: Referencing a Cell Using Text
|
1 |
=INDIRECT("A1") |
This formula will return the value from cell A1.
Example 2: Creating Dynamic Range References
|
1 |
=INDIRECT("Sheet1!A1:A5") |
This formula can be used to reference a range of cells in Sheet1, such as A1 to A5.
Example 3: Combining with Functions
|
1 |
=AVERAGE(INDIRECT("B1:B5")) |
You can use the INDIRECT function in combination with other functions. In this example, it calculates the average of values in cells B1 to B5.
Example 4: Using Cell References
|
1 |
=INDIRECT(A1) |
If cell A1 contains the text “B2,” this formula will reference the cell B2.
Excel INDIRECT is a versatile function that allows you to build dynamic references and work with data more efficiently. It’s particularly useful when you need to create dynamic formulas and interact with different parts of your worksheet.
