When working with Excel formulas, one of the most powerful and often overlooked functions is INDIRECT. It allows you to dynamically reference a cell, range, or even another worksheet by using text values. This level of flexibility can be a game-changer when dealing with dynamic data sets.
What is the INDIRECT function?
The INDIRECT function in Excel returns the reference specified by a text string. In other words, it converts a text-based cell reference into an actual reference that Excel understands.
The syntax of the function is:
INDIRECT(reference_text, [a1])- reference_text – A text string that represents a cell or range reference.
- a1 (optional) – A logical value that determines the reference style:
TRUE(default) – Uses A1 notation (e.g., “A1”).FALSE– Uses R1C1 notation (e.g., “R1C1”).
How INDIRECT works in Excel
To understand how INDIRECT works, let’s consider a simple example. Suppose we enter the following formula:
=INDIRECT("B2")If cell B2 contains the value 100, the formula effectively returns 100. Instead of directly referencing B2, we are using text to define the reference, making it dynamic.
Best INDIRECT examples
Referencing another sheet dynamically
The INDIRECT function allows us to reference data from another worksheet dynamically. Suppose we have a sheet named Sales and we want to get the value from A2 of that sheet.
=INDIRECT("Sales!A2")If the sheet name is placed in a cell, say B1, we can create a dynamic reference like this:
=INDIRECT(B1 & "!A2")Now, changing the name in B1 will automatically update the reference.
Using INDIRECT with named ranges
Named ranges improve formula readability and are compatible with INDIRECT. If we have a named range called SalesData, we can reference it dynamically:
=INDIRECT("SalesData")This pulls the data from the named range without directly including the cell addresses.
Building dynamic column or row references
We can also build dynamic references for columns or rows. Suppose we have row numbers in A1, and we need to reference column B dynamically:
=INDIRECT("B" & A1)If A1 contains 5, the function becomes:
=B5Using INDIRECT for dynamic range selection
Consider extracting data from a range dynamically based on user input. If the start and end rows are in C1 and C2, this formula selects a dynamic range:
=SUM(INDIRECT("A" & C1 & ":A" & C2))If C1 = 2 and C2 = 5, the range translates to A2:A5, summing the values in that range.
Key advantages of INDIRECT
- Dynamic references – Changes automatically based on input values.
- Refers to named ranges – Instead of hardcoding references, we can use names.
- Works across multiple sheets – Enables cross-sheet referencing without explicit links.
- Combined with other functions – Enhances
MATCH,INDEX, andVLOOKUPfor flexible lookups.
Limitations of INDIRECT
- Volatility – Recalculates every time the worksheet changes, affecting performance.
- Breaks with deleted references – If a referenced sheet or cell is deleted, errors occur.
- Does not work with closed workbooks –
INDIRECTcannot reference data from closed files.
Practical example: Dynamic table lookup
Suppose we have multiple sheets named January, February, and March, each containing sales data in column B. If a user selects the month in D1, we can retrieve their sales dynamically:
=INDIRECT(D1 & "!B2")This adjusts the lookup based on the chosen month, making reports more interactive.
Example table using INDIRECT
| Month | Value in B2 | Formula | Result |
|---|---|---|---|
| January | 500 | =INDIRECT(“January!B2”) | 500 |
| February | 600 | =INDIRECT(“February!B2”) | 600 |
| March | 550 | =INDIRECT(“March!B2”) | 550 |
Conclusion
The INDIRECT function in Excel provides powerful flexibility by allowing dynamic cell referencing based on text inputs. Whether you’re pulling data from multiple sheets, utilizing named ranges, or building interactive dashboards, INDIRECT can be an invaluable tool. However, due to its volatile nature, it’s best used with caution in large spreadsheets.
Other interesting article:
How ISBLANK works in Excel? Best ISBLANK examples