How to Use the INDIRECT Function in Excel?

What does the INDIRECT Function in Excel do?

Before learning how to use the INDIRECT function in Excel let’s have a look at what it does. The INDIRECT function in Excel is used to reference a cell or range indirectly using a text string. Instead of referring directly to a cell (e.g., =A1), it allows you to construct a reference dynamically, making your formulas more flexible.

Why is the INDIRECT function useful?

The INDIRECT function is particularly useful when working with dynamic references, dependent dropdown lists, and consolidating data from multiple sheets.

How to use the INDIRECT function in Excel?

Syntax:

=INDIRECT(ref_text, [a1])

• ref_text (Required): The text string representing a valid reference to a cell or range.
• a1(Optional): A logical value (TRUE or FALSE) indicating the type of reference:
  • TRUE (default) → Uses A1-style references (A1, B2, etc.).
  • FALSE → Uses R1C1-style references (R1C1, R2C3, etc.).

Example uses of the INDIRECT Function

Common Use Cases:

1. Referencing a Cell Dynamically
   • Example: Suppose cell B1 contains "D3" and you use: =INDIRECT(B1) This returns the value in cell D3 because INDIRECT treats "D3" as a reference.
2. Referencing Different Sheets Dynamically
   • Example: If A1 contains "Sheet2", then: =INDIRECT(A1 & "!B2") Returns the value in cell B2 from Sheet2.
3. Creating Dynamic Ranges
   • Example: If A1 contains "A1:A10", then: =SUM(INDIRECT(A1)) Sums the range A1:A10, even if the reference changes.
4. Making Dependent Dropdown Lists
   • Example: If you have categories in Column A (e.g., Fruits, Cars) and corresponding lists in different named ranges, INDIRECT allows a dropdown to update dynamically based on a user’s selection.

Example Dataset

• If D1 contains "A1", then: =INDIRECT(D1) Returns "Fruits" (since A1 contains “Fruits”).
• If D1 contains "C2", then: =INDIRECT(D1) Returns 15 (since C2 contains 15).

Limitations of INDIRECT

• It does not update automatically if referenced cells move (unlike direct references).
• Cannot refer to closed workbooks.
• It can be resource-intensive when used excessively in large datasets.