Introduction

INDIRECT function in excel is a reference function. It is used to change a text string to a valid reference in formula. It can be used when a reference in form of text need to be changed into a valid reference. This reference does not change when rows and columns are added into the sheet.

 Syntax

=INDIRECT(ref_text, a1 )

Arguments

• Refer_text:- It is the reference to a cell as a text string, a name as a reference, A1 style reference, R1C1 style reference. If an invalid reference is made INDIRECT returns #REF! as an error.
• a1:- It is a value which signifies what kind of reference is present in refer_text. If it is true than A1 style reference is defined. If it is false than A2 style reference is defined.

Keynotes

• INDIERCT function creates a reference which does not change even when cells are added or removed.
• When an external reference is used in ref_text, the workbook which it is referred to must be open
• a1 argument is either true for false, if true than it results in A1 style reference, if false it results into R1C1 style reference.
• By default a1 is true.
• If you are using Excel Web App than external reference cannot be made.

Examples

In this example, INDIRECT function is used as following:-

 1.Here formula =INDIRECT(A3) is used, here function searches for value of reference in cell A3, now reference is to cell B3 which has the value 9. Hence we get 9 as the value.

2. Here formula =INDIRECT(“B”&A7) is used, this formula combines “B” with the value of cell A7 , Now this value is 7 so “B” becomes B7, then reference is to cell B7 which has the value 51. Thus 51 is the output.