Quick Navigation

INDIRECT Function

Excel 2007+

Summary

The INDIRECT function dynamically converts text strings into actual cell references, enabling flexible formula creation that adapts to changing conditions without editing the formula structure itself.

Syntax

INDIRECT(ref_text, [a1])

Parameters

Parameter	Type	Required	Description
ref_text	`Text`	Yes	Text string defining the cell reference, range, or named range to evaluate
a1	`Logical`	No	TRUE for A1-style references (default), FALSE for R1C1-style references

Using the INDIRECT Function

INDIRECT transforms static text into live cell references, making it perfect for building dynamic reports, dashboards, and data models that automatically adjust as your data structure changes.

Common INDIRECT Examples

Basic Cell Reference

=INDIRECT(A1)

Returns value from cell reference stored in A1 (e.g., if A1 contains 'B2', returns B2's value)

Dynamic Column Reference

=INDIRECT("B"&ROW())

Creates reference to column B of current row (B1, B2, B3 etc.)

Named Range Access

=INDIRECT(A4)

Returns value from named range or cell whose name is in A4

Dynamic Sheet Reference

=INDIRECT("Sheet2!"&B1)

References cell in Sheet2 based on column in B1

Frequently Asked Questions

Occurs when ref_text isn't valid cell reference, external workbook is closed, or range exceeds Excel limits (1,048,576 rows, 16,384 columns).

Yes, INDIRECT recalculates when ref_text changes or referenced cells update.

Yes, but the external workbook must be open, otherwise returns #REF!

Common Errors and Solutions

#REF!

Cause: Invalid reference in ref_text or external workbook closed

Solution: Verify ref_text format and open external workbooks

#VALUE!

Cause: a1 argument not logical TRUE/FALSE

Solution: Use TRUE, FALSE, or omit [a1] parameter

Notes

External references not supported in Excel for Web
Requires exact A1 or R1C1 syntax in ref_text
Volatile function - recalculates every time worksheet changes
Performance impact in large models with many INDIRECT calls

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in:

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+