How to use the INDIRECT function

The INDIRECT function returns a reference specified by a text string, evaluates that reference and displays its contents. The function is useful when you want a cell reference in a formula to be variable, without the need to change the formula every time.

Syntax

INDIRECT(ref_text,a1)

ref_text: a text that contains a cell reference.
a1: a logical value that specifies what type of reference is contained in the cell ref_text.
• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. E.g. C12
• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference. E.g R12C3

Example 1:

indirect1

Cell B1 contains the formula: =INDIRECT(A1)
Cell A1 contains the text: “C6”
Cell C6 contains the value: 135
The INDIRECT function will evaluate the reference as: = C6, which is equal to 135.
If you change the text in cell A1 to “C3” then you will get 198 in B1.

Example 2:

indirect2

The data shown above is a snapshot from a sheet named “Sheet2″ in a workbook.

Cell B1 contains the formula: =INDIRECT(A1&”!”&”C8″)
Cell A1 contains the text: “Sheet2”
Cell C8 contains the value: 1922
The INDIRECT function will evaluate the reference as: = Sheet2!C8, which is equal to 1922.
If you change the text in cell A1 to “Sheet1” then the reference will be evaluated as: = Sheet1!C8 and you will get whatever value in Cell C8 of sheet1.

Comments

  1. I am not new to blogging and actually value your web site. There is much innovative content that peaks my interest. I am going to bookmark your site and keep checking you out.

  2. Thanks for your interest.

  3. Dhanraj114 says:

    Wow…!!!
    Nice explanation.
    Thanks

  4. nirmal agarwal says:

    Very simple & useful explanation to understand this function.Thanks

  5. Thanks.

    I am glad that I made myself clear.

  6. Lynn Huras says:

    Just wondering if you know of an alternative to INDIRECT for using in a formula to pull data from another worksheet? I love INDIRECT, but when you need to use is a large number of times in multiple worksheets, it really bogs down your system constantly recalculating.

  7. Thanks for the post. it was really useful for me. Can you explain further uses of Indirect function

    • Check This article. It may be helpful.

      • Can someone tell me whats wrong with this formula.
        =IF(C104,SUMIF(Data!L2:L10468,C11,INDIRECT(“Data!”&VLOOKUP($A$9,$M$4:$R$6,4,FALSE)&”2″&VLOOKUP($A$9,$M$4:$R$6,4,FALSE)&”10468”)),SUM(INDIRECT((“Data!”&VLOOKUP($A$9,$M$4:$R$6,4,FALSE)&”2″&VLOOKUP($A$9,$M$4:$R$6,4,FALSE)&”10468”))))

      • Please, what are you try to get from this complex formula and what kind of error did you get.