There are hundreds of functions in MS-Excel. There are a few of them which we use frequently and INDIRECT function is one among them. Indirect function is under the category of lookup or reference function. Let us know the importance of indirect function and see how to use Indirect function in excel and its usage in different scenarios.
What is Indirect Function in Excel
As the name says, Indirect function in excel 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.
Suggested Reading : Know more about cell, range, worksheet or a workbook
In this post, let us see the syntax, and how Indirect function in excel works in detail.
Syntax of Indirect Function in Excel
The Syntax for INDIRECT function is as follows
=INDIRECT(ref_text,[a1])
Understanding Syntax of Indirect
Indirect function has only two arguments
Ref_text : This can be a cell reference, text or string or even both which is used to create a reference to a cell or a range. The range which is being referenced can again be a cell or a text or a range or a Named Range
Suggested Reading : Named Ranges – Know more about Named Ranges in Excel.
[a1] : This is a logical value which takes the value of True or False.
- If this is set to True or without a value 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.
Note: Except in a few cases we will follow 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 excel.
Example 1
In the above image 1st formula is Indirect(B2). Breaking down the formula
B2 is the ref text. True/ False option is empty. This means excel understands it as normal referencing style and returns the value in cell B2 i.e. 25.
Example 2
However, the 2nd formula – Indirect(B3,False).
B3 is the ref text and Cell B3 has r4c1 in it and the logical value is False. This indicates that cell naming convention is following the R1C1 referencing style. Excel evaluates the formula as follows.
Indirect(B3,False) = Indirect(r4c1,False) = 25 (Value in the intersection of Row 4 and Column 1)
Example 3
In the 3rd Case, Indirect(B5,False).
Here 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.
- Indirect(B5,False) = Indirect(r2c,False)
- Formula is in column C for which the number is 3. Therefore excel reads the formula as Indirect(R2C3,False)
- False states that the reference is R1C1 style. Therefore Indirect(R2C3) is the value in the intersection cell of 2nd row 3rd column (which is nothing but value in Cell C2 in the normal referencing Style) which is 25.
Unfortunately, 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 one of my earlier posts I explained about creating and using named ranges in excel along with the advantages of using named ranges.
Suggested Reading : Understand How to Create and use Named Ranges in Excel
There are five products and respective sales details for different quarters from column B to G. Let us create named ranges product wise (product1 to product5).
In cell C10, I have a dropdown to choose the product I want to look data for. (Suggested Read: How to Create Dropdowns in Excel)
In cells B11 and B12 We have Total Sales and Max sales volume. We need to now calculate the corresponding values in Cells C10 and C11 for the respective product. (We can use Sum and Max functions to get the details.)
How to use other functions or formulas with Indirect Function
How to link the Sum and Max formulae to the product cell to get the details for the product you select from the dropdown using Indirect function.
Wherever you have a named range created you can directly use Indirect function along with the formula or function instead of referring or 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.
Hence for Total Sales we need to write the formula as = 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 we select a different product from the drop down.
Similarly we can create cell references using Indirect function with a text also. Let us assume
- Cell D2 has value 10 and C10 has value 100. Now the formula Indirect(“C”&D2) returns the value 100.
- Similarly, if 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 while using Indirect Function
Indirect function returns a #REF error if the cell reference is not valid or the range of cells exceeds beyond the row limit of 1,048,576 or column limit of 16,384 or the reference is not open (when referred to other workbooks)
Sample Files
Click the below button to Download the sample file
Do you use INDIRECT function? – Please do share the situations or cases where you use Indirect function in the comment box below.
Do share this post with your friends if you find it useful.