INDIRECT Function

Turns text that looks like an address into a real reference.

Category

Lookup & Reference

Available in

All versions, Microsoft 365

Returns

A reference

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Reference Functions module

Overview

INDIRECT converts a text string such as "B5" or "March!C2" into a working reference. That lets you build references from other cells — for example, picking a sheet by name from a dropdown.

INDIRECT is volatile: it recalculates on every change, which can slow large workbooks.

Syntax

=INDIRECT(ref_text, [a1])

Arguments

ArgumentRequired?What it does
ref_textYesText containing a cell reference, range or defined name.
a1OptionalTRUE or omitted = A1 style. FALSE = R1C1 style.

Examples

Example 1 — Reference built from a cell

A1 holds the text "C3". INDIRECT returns whatever is in C3.

B1fx=INDIRECT(A1)
ABC
1C3Done
2
3Done
=INDIRECT(A1) Explain

Example 2 — Pulling from a sheet chosen in a dropdown

A1 contains a month name matching a sheet name.

=SUM(INDIRECT("'" & A1 & "'!B2:B50")) Explain
Always wrap the sheet name in single quotes so names with spaces work.

Common mistakes

  • #REF! errors. The text isn't a valid address, or it points at a closed workbook.
  • References don't update. Because the address is text, inserting rows won't adjust it.
  • Slow workbooks. Heavy use of INDIRECT recalculates constantly. Consider INDEX or CHOOSE instead.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.