Excel Function Translator Logo
    The Excel Translator

    INDIRECT

    Lookup & Reference

    Returns the reference specified by a text string. Allows dynamic cell references.

    Translations
    EnglishINDIRECT
    FrenchINDIRECT
    SpanishINDIRECTO
    GermanINDIREKT
    ItalianINDIRETTO
    PortugueseINDIRETO
    DutchINDIRECT
    PolishADR.POŚR
    RussianДВССЫЛ
    TurkishDOLAYLI
    CzechNEPŘÍMÝ.ODKAZ
    HungarianINDIREKT
    SwedishINDIREKT
    DanishINDIREKTE
    FinnishEPÄSUORA
    Syntax
    INDIRECT(ref_text, [a1])

    Arguments

    • ref_textText string representing a cell reference
    • a1TRUE for A1 style, FALSE for R1C1 style(optional)
    Examples
    =INDIRECT("A"&B1)
    Dynamic reference

    References cell A[value in B1]

    =SUM(INDIRECT(A1&":"&A2))
    Dynamic range sum

    Sums a range specified by text

    =INDIRECT("'"&A1&"'!B2")
    Dynamic sheet

    References different sheets

    Tips & Best Practices
    • •Powerful for dynamic ranges and sheet references
    • •Volatile function - recalculates every time
    • •Returns #REF! if reference is invalid
    Common Mistakes
    • •Sprinkling INDIRECT throughout a large workbook without realizing it's volatile - it recalculates on every single change, not just its own inputs, and that adds up
    • •Forgetting the single quotes around a sheet name that contains spaces, which INDIRECT needs to parse the text correctly
    • •Mixing up A1 and R1C1 style in the a1 argument and getting a #REF! error that doesn't explain what actually went wrong
    Related Functions
    OFFSETAnother volatile one for dynamic ranges - INDIRECT builds a reference from text, OFFSET shifts from a starting point instead
    ADDRESSBuilds the text address that INDIRECT then turns into a real reference
    VLOOKUPOften a cleaner way to get a dynamic sheet lookup, paired with CHOOSE or a helper table, without INDIRECT's overhead
    Frequently Asked Questions

    Why do people warn against overusing INDIRECT?

    Because it's volatile - Excel reruns it on every recalculation of the sheet, whether or not anything it actually depends on changed. Scatter it across a big workbook and you'll feel it in calculation speed.

    Why does INDIRECT return #REF!?

    Usually because the text string doesn't point to anything real - a typo in a sheet name, a missing quote around a name with spaces, or a reference to a workbook that isn't currently open (INDIRECT can't reach closed files).

    Can INDIRECT pull data from another workbook?

    Only while that workbook is open. The moment it's closed, INDIRECT loses access - unlike a normal cell reference, which can still show the last known value.

    Need to translate a formula using INDIRECT?

    Use our translator to convert your complete formula