INDIRECT function

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.


The syntax of the INDIRECT function is as follows:

=INDIRECT(ref_text, [a1])


ref_textThe 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

This formula will return the value from cell A1.

Example 2: Creating Dynamic Range References

This formula can be used to reference a range of cells in Sheet1, such as A1 to A5.

Example 3: Combining with Functions

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

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.