Lookup & Reference

INDIRECT Function in Excel

Returns the reference specified by a text string. References are immediately evaluated to display their contents.

Syntax

  • =INDIRECT(ref_text, [a1])

Arguments

  • ref_text (required): A text string that contains a cell reference
  • a1 (optional): TRUE for A1-style, FALSE for R1C1-style reference

Examples

  • =INDIRECT("A1") - Return value of cell A1 - Result: Value in A1
  • =INDIRECT("Sheet2!B5") - Reference cell on another sheet - Result: Value from Sheet2
  • =SUM(INDIRECT("A1:A"&B1)) - Dynamic range based on cell value - Result: Sum of dynamic range

Excel INDIRECT function documentation

  • INDIRECT is a Lookup & Reference function that converts a text string into a cell reference Excel can use in formulas.
  • Syntax: =INDIRECT(ref_text, [a1]). Available in all modern Excel versions.
  • Enables dynamic sheet names, rolling month columns, and template-driven models built from dropdown selections.
  • Related: [OFFSET](/functions/offset/), [INDEX](/functions/index/), Excel Tables for safer dynamic ranges.

INDIRECT syntax and reference styles

  • ref_text (required): text that looks like an address — "B2", "Sheet2!A1", or a named range name.
  • a1 TRUE (default): A1-style references; FALSE uses R1C1-style notation.
  • Combine with dropdown: =INDIRECT("Sheet"&A1&"!C5") when A1 holds 1, 2, or 3.
  • Sheet names with spaces need quotes inside the string: =INDIRECT("'Sales Q1'!A1").

Step-by-step: dynamic sheet reference with INDIRECT

  • Step 1 — Create sheets Jan, Feb, Mar and a summary sheet with month selector in A1.
  • Step 2 — Formula: =INDIRECT(A1&"!B10") pulls B10 from the sheet named in A1.
  • Step 3 — Validate each sheet name matches the dropdown exactly (case-insensitive but spelling matters).
  • Step 4 — Wrap with IFERROR only after confirming #REF! is not from deleted sheets.
  • Step 5 — Consider Excel Table structured references instead of INDIRECT for new models.

INDIRECT vs OFFSET vs INDEX

  • INDIRECT — text-driven references; volatile; powerful for sheet/column pickers.
  • OFFSET — shifts from a base cell by rows/columns; also volatile.
  • INDEX — non-volatile; often clearer for bounded dynamic ranges inside one sheet.
  • Both INDIRECT and OFFSET recalculate often — avoid on thousands of rows if performance matters.

Worked examples to copy

  • Rolling column letter: =INDIRECT("R"&ROW()&"C5", FALSE) in R1C1 mode.
  • Sum across selected sheet: =SUM(INDIRECT(A1&"!$B$2:$B$100")).
  • Named range from cell: =INDIRECT(B1) when B1 contains a defined name string.
  • Column from header row: =INDIRECT(ADDRESS(2,MATCH("Revenue",$1:$1,0))) alternative without INDIRECT.

People also ask

  • Why INDIRECT #REF!? — Invalid text address, deleted sheet, or malformed quotes.
  • Is INDIRECT volatile? — Yes. It recalculates when any cell changes.
  • INDIRECT with Excel Tables? — Prefer structured references =Table1[Column] when possible.
  • Can INDIRECT reference closed workbooks? — No. Only open workbook addresses.

Common errors

  • #REF! when reference is invalid
  • #NAME? when sheet name has spaces without quotes

Use cases

  • Dynamic sheet references
  • Building formulas from text
  • Creating flexible reports

Frequently asked questions

  • What is INDIRECT used for in Excel? INDIRECT converts a text string into an actual cell reference. This allows you to build dynamic references that change based on cell values, create references to other sheets dynamically, and build flexible formulas.
  • How do I reference another sheet with INDIRECT? Use =INDIRECT("'Sheet Name'!A1") with single quotes around sheet names containing spaces. For dynamic sheet names: =INDIRECT("'"&A1&"'!B5") where A1 contains the sheet name.
  • Why is INDIRECT considered volatile? INDIRECT recalculates every time Excel recalculates, even if its inputs haven't changed. This can slow down large workbooks. Consider INDEX/MATCH as a non-volatile alternative when possible.
  • How do I create a dynamic named range with INDIRECT? Combine INDIRECT with COUNTA for dynamic ranges: =INDIRECT("A1:A"&COUNTA(A:A)). This creates a range that automatically expands as you add data.
  • Can INDIRECT reference a closed workbook? No, INDIRECT cannot reference closed workbooks. The referenced workbook must be open. For closed workbook references, use Power Query or VBA instead.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use INDIRECT

  • Dynamic sheet references — common Lookup & Reference scenario for INDIRECT.
  • Building formulas from text — common Lookup & Reference scenario for INDIRECT.
  • Creating flexible reports — common Lookup & Reference scenario for INDIRECT.

INDIRECT in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • INDIRECT syntax: =INDIRECT(ref_text, [a1])
  • ref_text (required): A text string that contains a cell reference
  • a1 (optional): TRUE for A1-style, FALSE for R1C1-style reference
  • Confirm INDIRECT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm INDIRECT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • ADDRESS (/functions/address/): Creates a cell reference as text from row and column numbers.
  • OFFSET (/functions/offset/): Returns a reference offset from a starting point by a specified number of rows and columns.
  • INDEX (/functions/index/): Returns the value of an element in a table or array, selected by the row and column number indexes.
  • MATCH (/functions/match/): Returns the relative position of an item in an array that matches a specified value.

Common problems and fixes

  • Why Is My Excel Formula Not Calculating? (/problems/formula-not-calculating/): Press F9 or switch to Automatic calculation, re-enter the formula in General format, and confirm the referenced ranges still exist.

Errors to watch for

  • #REF! when reference is invalid — review causes on linked error pages in the directory.
  • #NAME? when sheet name has spaces without quotes — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter INDIRECT on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

INDIRECT worked examples to copy

  • =INDIRECT("A1") — Return value of cell A1. Expected result: Value in A1.
  • =INDIRECT("Sheet2!B5") — Reference cell on another sheet. Expected result: Value from Sheet2.
  • =SUM(INDIRECT("A1:A"&B1)) — Dynamic range based on cell value. Expected result: Sum of dynamic range.

INDIRECT reference summary for crawlers and offline review

  • INDIRECT belongs to the Lookup & Reference category in Excel. Returns the reference specified by a text string. References are immediately evaluated to display their contents.
  • Full syntax: =INDIRECT(ref_text, [a1]). Open /functions/indirect/ for parameters, FAQs, and related pages.
  • Common mistakes: #REF! when reference is invalid; #NAME? when sheet name has spaces without quotes
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.