INDIRECT

The INDIRECT function is used to return the reference specified by a text string. It is commonly employed in Excel to dynamically reference cells or ranges based on the contents of another cell.

Syntax

=INDIRECT(ref_text, [a1])

ref_text A text string that contains a cell reference a defined name or a range reference.
a1 (Optional) A logical value that specifies the type of reference to return. TRUE or omitted indicates A1-style reference and FALSE indicates R1C1-style reference.

About INDIRECT

When you find yourself in need of dynamically changing references in Excel, turn to the versatile INDIRECT function. It offers a smart solution for referencing cells or ranges based on textual input, providing the flexibility required in various data manipulation and analysis scenarios. Whether you're constructing complex formulas or designing interactive spreadsheets, INDIRECT proves to be a handy tool for seamless referencing based on textual criteria.

To effectively use INDIRECT, you provide a text string that encodes the desired reference. This could be a cell address, a named range, or a reference to a specific range within the workbook. The function evaluates this text string and returns the reference it represents, enabling dynamic linking between cells and ranges.

One of the key features of INDIRECT lies in its ability to adapt referencing styles. By toggling the optional [a1] argument, you can choose between A1-style (default) or R1C1-style referencing, addressing diverse user preferences and requirements.

INDIRECT serves as a valuable asset when working with changing datasets or when building interactive dashboards. It empowers you to create dynamic formulas that adjust their references based on user input or changing conditions, enhancing the robustness and interactivity of your Excel models.

Examples

Suppose you have a list of product names in cells A2:A5 and corresponding sales data in cells B2:B5, with the product name you want to reference stored in cell C1. To dynamically fetch the sales amount based on the product name in C1, you can use the following INDIRECT formula:

=INDIRECT("B"&MATCH(C1,A2:A5,0)+1)

Consider a scenario where you have data organized in multiple sheets within the same workbook, each sheet representing a different region's sales figures. If the regional sheet names match the region names stored in cells A2:A4, you can dynamically sum the sales from the selected region using INDIRECT. The formula can be:

=SUM(INDIRECT(A2&"!C2:C100"))

Questions

How does the INDIRECT function work in Excel?

The INDIRECT function in Excel evaluates a text string provided as input and returns the reference it represents within the workbook. This enables dynamic referencing based on textual content, allowing for flexible and interactive data manipulation.

Can the INDIRECT function be used to dynamically reference cells from different sheets?

Yes, the INDIRECT function can be utilized to dynamically reference cells or ranges from different sheets within the same workbook. By constructing appropriate text strings that encode the sheet name and cell references, you can retrieve data from various sheet locations.

Is it possible to use the INDIRECT function to create dynamic named ranges?

Yes, you can leverage the INDIRECT function to create dynamic named ranges in Excel. By constructing text strings that specify the range boundaries or utilizing cell contents to define the range, you can dynamically adjust named ranges based on changing criteria.

What precautions should be taken when using the INDIRECT function in Excel?

When employing the INDIRECT function, ensure that the text string provided as input results in a valid reference within the workbook. Avoid circular references, external workbook references, and potential errors due to incorrect string formatting. Exercise caution when dynamically referencing cells to preserve data accuracy and formula reliability.

Related functions

ADDRESS cell COLUMN HLOOKUP INDEX LOOKUP MATCH OFFSET ROW VLOOKUP