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. admin says:

    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. admin says:

    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. Ramesh says:

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

    • admin says:

      Check This article. It may be helpful.

      • Patrick says:

        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″))))

      • admin says:

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