INDIRECT Function in Excel: Build a Reference From Text

⏱ 1 min readUpdated 28 September 2026

LookupLevel: ExpertAvailable in: Excel 2007+

In this article
  1. Syntax
  2. Examples
  3. Example 1
  4. Example 2
  5. Common errors and fixes
  6. Related functions

INDIRECT turns text such as “Mar!B5” into a real reference — the key to dependent dropdowns and summary sheets that switch between monthly tabs.

Syntax

=INDIRECT(ref_text, [a1])
Argument What it means
ref_text A reference written as text.

Examples

Example 1

=INDIRECT(A2 & "!B5")

B5 on the sheet named in A2.

Example 2

=INDIRECT($B2)

Data-validation source for dependent dropdowns (named ranges).

Common errors and fixes

You see Why, and the fix
#REF! Sheet name misspelled, or contains spaces without quotes: use “‘” & A2 & “‘!B5”.
💡 Volatile and breaks silently if sheets are renamed — use sparingly.

OFFSET · INDEX · ADDRESS

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Keep reading

Excel Functions A–ZSORT Function in Excel: Sort Data With a FormulaExcel Functions A–ZROUND Function in Excel: Round to Any Number of DigitsExcel Functions A–ZEOMONTH Function in Excel: Last Day of a Month
✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong