Excel INDIRECT Function (Text to Reference) – Examples & Practice
Practice the Excel INDIRECT function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Convert text "B4" into a real reference and return the value from cell B4.
Formula Syntax
=INDIRECT(ref_text, [a1])
- ref_text: A text value that evaluates to a reference (for example "B4" or "Sheet2!A1").
- [a1]: Optional logical: TRUE (default) for A1 style, FALSE for R1C1 style.
What it does
INDIRECT converts a text string into a worksheet reference and returns the value at that reference. It enables dynamic workbooks where the target cell or range is chosen by formula logic.
Excel INDIRECT Function Examples
Reference from text
=INDIRECT("B4")
Returns the value in B4 by interpreting the string as an address.
Sheet + cell text
=INDIRECT("Summary!B10")
Pulls B10 from the Summary sheet when the name is valid.
R1C1 style (optional)
=INDIRECT("R4C2", FALSE)
Uses row/column notation when the second argument is FALSE.
dynamic-array-data.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Category | Amount | Region |
| 2 | A | 120 | North |
| 3 | B | 340 | South |
| 4 | A | 560 | North |
| 5 | C | 225 | West |
| 6 | B | 90 | South |
| 7 | Output |
Input Formula
Need Help?
Tips
- Prefer **XLOOKUP**, **FILTER**, or tables when they replace fragile INDIRECT chains.
- Logically validate dropdown values before feeding INDIRECT.
- Document volatile dependencies for teammates.
INDIRECT Function Use Cases
- Choose a range name from a dropdown and aggregate that range
- Rotate month tabs with predictable naming (
Jan,Feb, …) - Legacy dashboards that must stay in pure grid formulas
- Teaching how references are evaluated
- Bridge patterns before modern dynamic arrays
Common mistakes - INDIRECT function not working
- Forgetting quotes around sheet names with spaces
- Building text that points to nothing (
#REF!) - Volatile chains that recalc too often
- Typos in address strings from concatenation
- Security concerns when text comes from user input
FAQ
Why is INDIRECT volatile?
Excel recalculates INDIRECT whenever the sheet recalculates because the target reference is computed at runtime.
INDIRECT vs OFFSET?
Both can build dynamic references. INDIRECT resolves text addresses; OFFSET shifts from a base cell by rows and columns.
Can INDIRECT point to another sheet?
Yes, if the text includes a valid sheet name such as `Sheet2!A1` (quote sheet names with spaces).
Does INDIRECT work with closed workbooks?
Generally no—references must be available to the open calculation context.
Is INDIRECT bad for performance?
Heavy use across large models can slow workbooks; prefer structured references or Power Query when possible.
Comparison
| Tool | Input | Output |
|---|---|---|
| INDIRECT | Text address | Reference |
| OFFSET | Base + offsets | Reference |
| INDEX | Coordinates | Value |
Example
=INDIRECT("B4")=OFFSET(B2, 2, 0)
Advanced examples
INDIRECT with named ranges
Put Sales_Q1 in A1 and use =SUM(INDIRECT(A1)) to point SUM at whichever named block is selected (validate names carefully).
Combine with ADDRESS
=INDIRECT(ADDRESS(4,2)) builds B4 programmatically from row/column numbers.