Home / Excel Functions / Excel INDIRECT Function

Excel INDIRECT Function

 
Excel INDIRECT Function, with Examples:
 
—————————————————————————————————————————–
 
Excel INDIRECT Function:
The INDIRECT function returns the result of a specified reference.
 
Syntax: INDIRECT(text_reference, reference_type)
 
1. text_reference is a reference to a cell and that cell can contain: (i) an A1-style reference; (ii) an R1C1-style reference; (iii) a named range;  or (iv) a text string which provides reference to a cell.
 
2. If reference is to another workbook, that workbook should be open else the function will return a #REF! error.
 
3. reference_type specifies the type of reference: text_reference is a reference to a cell and that cell is considered to contain an R1C1 style reference if reference_type is FALSE, else it will be considered to contain an A1 style reference if reference_type is omitted or TRUE.
 
An INDIRECT function within a formula can change a cell reference without changing the formula itself, because the INDIRECT function creates a cell reference from text in another cell. Consider the formula =IF(INDIRECT(A2)>10,”Y”,”N”) and if cell A2 contains the text B3, then the content of cell B3 is evaluated. Now without changing the formula, text contained in cell A2 can be changed to B4 and then the content of cell B4 will get evaluated with the same formula.
 
A reference to a cell within a formula gets updated if the cell is moved by cut or delete commands or by inserting rows or columns. By using the INDIRECT function, the cell reference will remain unchanged. Consider the formula =IF(B5>10,”Y”,”N”)  and if a row is inserted above B5, then the formula will change to  =IF(B6>10,”Y”,”N”). However, if the formula is =IF(INDIRECT(“B5”)>10, “Y”, “N”), then the formula will always evaluate contents of cell B5.
 
Example 1 – Reference from a Cell Value:
 
Table 1
1.333  ->  =INDIRECT(A2)  [Formula]
 
Formula uses indirect function for reference from a cell value, and returns 1.333 (Refer Table 1).
 
Formula returns value of the reference in cell A2.
 
Cell A2 contains the text: “B2”.
 
Cell B2 contains the value: 1.333. The INDIRECT function evaluates the reference as: = B2, which is equal to 1.333.
 
Changing the text in cell A2 to “B3″ will return 45.
 —————————————————————————————————————————-
 Example 2 – Reference from a Cell Value and Text:
Table 2
=INDIRECT(“B”&A5)  [Formula]
 
Formula uses indirect function for reference from a cell value and text, and returns 62 (Refer Table 2).
 
Formula evaluates value of a reference in cell A5.
 
=INDIRECT(“B”&A5) evaluates to “=B5”.
 
Cell B5 contains the value: 62. The INDIRECT function evaluates the reference in cell A5 as 5, and returns value in cell B5 which is 62.
——————————————————————————————————
 
Example 3 – Reference to a Named Range:
 
Table 3a
=INDIRECT(A4)  [Formula]
 
Formula uses indirect function for reference to a named range. Cell A4 contains the text: “George”.
 
Cell B4 (is a named range) has the defined name “George” (refer Table 3b), and the value of the defined name is returned.
 
Cell B4 contains the value: 10. The INDIRECT function evaluates the reference as: = B4, which is equal to 10 (Table 3a).
 
Table 3b
 
——————————————————————————————————-
 
Example 4 – Reference To a Different Sheet: 
 
Table 4
=INDIRECT(A6&”!”&”B6″)  [Formula]
 
Formula uses indirect function for reference to a different worksheet, and returns 101 (Refer Table 4).
 
The Table 4 is a snapshot from the worksheet named “Sheet1”.
 
Formula evaluates value of a reference in cell A6.
 
=INDIRECT(A6&”!”&”B6″) evaluates to: =Sheet1!B6.
 
Cell B6 contains the value 101. The INDIRECT function evaluates the reference in cell A6 as Sheet1, and returns value in cell B6 (in Sheet1 viz. =Sheet1!B6) which is 101.
 ——————————————————————————————————————————-
 
Example 5 – Reference To a Different Workbook:
 
Table 5
=INDIRECT(“[“&A7&”]” & A6 & “!” & “B7”)  [Formula]
 
Formula uses indirect function for reference to a different workbook, and returns 99 (Refer Table 5).
 
The INDIRECT function can create linked references to other workbooks, but will return a #REF! error if that workbook is closed. 
 
The table is a snapshot from the workbook named “Workbook1.xls” and worksheet named “Sheet1”.
 
Formula evaluates values of references in cells A7 and A6.
 
=INDIRECT(“[“&A7&”]”&A6&”!”&”B7″) evaluates to: =[Workbook1.xls]Sheet1!B7.
 
Cell B7 contains the value: 99. The INDIRECT function evaluates the reference in cell A7 as: Workbook1.xls, and in cell A6 as: Sheet1, and returns value in cell B7 (viz. =[Workbook1.xls]Sheet1!B7) which is 99.
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top