There are hundreds of functions in MS-Excel. There are a few of them which are used very frequently and INDIRECT function is one among them. Indirect function is categorized as lookup/ reference function. Today let’s know the importance of indirect function and see how to use Indirect function in excel and its usage in different scenarios.
As the name says, Indirect function is used to refer a cell, range, worksheet or a workbook indirectly. It is important to note that the references won’t change even if we insert a row or column in between.
In this post, let’s see the syntax, and how Indirect function works in detail.
Syntax
The Syntax for INDIRECT function is as follows:
=INDIRECT(ref_text,[a1]) |
Indirect function has only two arguments
Ref_text : This can be a cell reference, text/ string or even both which is used to create a reference to a cell/ range. The range which is being referenced can again be a cell/ text/ range or Named Range (Read this post to know more about Named Ranges).
[a1] : This is a logical value which takes the value of True or False.
If this is set to True/ left without entering the referencing style will be the normal one which is A1, B1 etc.
If it is set to False the referencing style would be the R1C1 referencing style.
Except in a few cases we will be following the normal referencing style instead of R1C1 referencing style.
Understanding the referencing using Indirect.
See the below image which shows the basic use of indirect function.
In the 1^{st} formula, Indirect(B2), B2 is the ref text and True/ False is left blank which means excel understands it as normal referencing style and returns the value in cell B2 i.e 25.
However, the 2^{nd} formula, Indirect(B3,False) B3 is the ref text and Cell B3 has r4c1 in it and the logical value is set to False. This indicates that R1C1 referencing style is used in the cell naming convention. Excel evaluates the formula as follows.
Indirect(B3,False) = Indirect(r4c1,False) = 25 (Value in the intersection of Row 4 and Column 1)
In the 3^{rd} Case, Indirect(B5,False) where B5 has r2c (No number specified for the column).
In such cases Excel takes the column name where we write the Indirect formula and gives the numeric equivalent of the column and returns the respective call value. Let’s Evaluate the formula to understand it better.
Step1: Indirect(B5,False) = Indirect(r2c,False)
Step2: Formula is written in column C for which the number is 3. Therefore formula is read by excel as Indirect(R2C3,False)
Step3: False states that the reference is R1C1 style. Therefore Indirect(R2C3) is the value in the intersection cell of 2^{nd} row 3^{rd} column (which is nothing but value in Cell C2 in the normal referencing Style) which is 25.
There is no specific explanation for why excel takes the number of column in which the formula is written.
Using Named Ranges with Indirect Function in Excel
In my earlier posts I explained about creating and using named ranges in excel along with the advantages of using named ranges. (In case if you are not aware of named ranges Read: to understand more about Named Ranges.)
See the data in the below image.
There are five products and respective sales details for different quarters from column B to G. I created named ranges product wise (product1 to product5).
In cell C10, I have a dropdown to choose the product I want to look data for. (Read: How to Create Dropdowns in Excel)
In cells B11 and B12 I have Total Sales and Max sales volume to be displayed in Cells C10 and C11 for the selected product. (We can use Sum and Max functions to get the details.)
How to link the Sum and Max formulae to the product cell to get the details for the product selected from the dropdown using Indirect function.
Where ever you have a named range created you can directly use Indirect function along with the formula/ function instead of referring/ selecting the entire range. Let us use the Indirect function with Sum function to understand in detail.
Inside the sum function where you pass the arguments. Write the Indirect function referring to the cell having product name.
i.e. for Total Sales = Sum(Indirect(Product Name))
We have the product name in cell C10, so our formula will be =Sum(Indirect(C10)).
Since we already created Named Ranges for all the product names the value changes as and when the product is changed.
Similarly we can create cell references using Indirect function with a text also. Let’s assume cell D2 has value 10 and C10 has value 100. Now the formula Indirect(“C”&D2) returns the value 100.
D2 has value 10 then formula changes as follows =Indirect(“C”&10) =Indirect(C10) and C10 has a value 100. Therefore the return value will be 100.
Error Handling using Indirect Function
Indirect function returns a #REF error if the cell reference is not valid/ the range of cells exceeds beyond the row limit of 1,048,576 or column limit of 16,384/ the referred is not open (when referred to other workbooks)
Download the Practice Workbook used in Example
Do you use INDIRECT function in Excel ?
Please do share the situations/ cases where you use Indirect function in the comment box below.
Do share this post with your friends if you find it useful.