INDIRECT Function In Excel
Indirect function in excel is an inbuilt function which is used to reference and get cell values from a text string, this formula takes reference from the referred cell, it has two arguments the first argument is not optional while the second argument is optional which refers to the type of match, this function can also be used with another formula.
INDIRECT function in excel has multiple usages. It returns the cell reference specified by a text string. For example, if cell B1 contains a string, let say c4 and c4 range have the value “Jessica”. Now if we will use the INDIRECT function and pass the reference text as B1, it will return the value contained by cell range c4 that is “Jessica”.
Excel’s INDIRECT function lets you specify a cell address INDIRECTly. As you can in the above INDIRECT Excel example, if cell B1 contains the text C4, this indirect excel formula returns the content of the cell C4 (Jessica). This function is an extremely useful function. You can use INDIRECT function whenever you want to change the reference to a cell within an indirect excel formula without changing the formula itself.
INDIRECT Excel Formula
Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string.
a1 argument is optional
|a1||False or 0, when ref_text is in R1C1 style|
|a1||Omitted or True (1) when ref_text is in A1 style|
How to Use INDIRECT Function in Excel?
Let’s take an INDIRECT excel example, before using the address function in Excel workbook:
Suppose, for a website www.xyz.com we have the Google analytics data for visitors to the website from different audience channel for five different countries as shown below in the table:
We can use the INDIRECT function in Excel for the purpose of converting a text string into a reference, so referring to cell H4 and using that name as the reference. Use of INDIRECT function in excel, in this case, is to reference name ranges using values of a cell, so in cell I4, we could simply use the sum function to calculate the total number of visitors from a country Canada.
Now, we can directly calculate the sum of visitors from each country using sum function, but using the INDIRECT function in excel we can calculate the sum of visitors from different countries by changing the name reference rather than changing the indirect excel formula itself.
We have created the name range for each country visitor; this could be done easily by typing the name range in the space just below the clipboard as shown below
Another method is you can go to formulas->name manager -> select the range you want to name ->click on name manager ->type the name range
Similarly, created name range for visitors from other countries as well
Now, in I4, calculating the sum of the visitor from country Canada by using the INDIRECT function and using the name range in H4 (Canada, a name above for F3:F7), we get the total sum of visitor
If we create a data list using the data validation for the countries and apply that list to H4
And changing the different country name from Canada to India, USA, Australia or Singapore, we get the total sum of the visitor in I4 using the INDIRECT function in Excel.
So, for each case we are not changing the indirect excel formula which is in cell I4, we are changing the name reference in H4 and calculating the sum of the visitors for each country.
We can move one step further and can reference worksheet- level names also using the INDIRECT function in excel. For example, we will now use the INDIRECT function in reference to different sheets in the workbook
We have created the different sheets with the country names for the totals number of the sales made with each search channel for different countries
Similarly for other countries
Again, we will be calculating the total sales made through each channel using the INDIRECT function in Excel for different sheets with country name as a reference.
In this case, since we have sales data in different sheets, we will need to concatenate different elements that are the data range, so the ref_text will start with the name of the sheet which is India in this case in B4, then we will use the & operator called ampersand which is used for concatenation.
So, we get the total sum of the sales for India, now if we change the sheet country name in B4 from India to other countries like USA, Singapore or Canada we will get the total sale value of those countries.
Again, in this case, we have simply changed the ref_text values in cell B4 without changing the main indirect excel formula which is in C4, to get the sum of sales for different countries that are in different sheets using the INDIRECT function.
Now, we are going to use the INDIRECT function using the R1C1 reference to get the last month sale value. We have sales for different months in columns B, C, and D for three months Jan, Feb, and Mar that can grow to furthermore months like Apr, May, June, and so on. So, the sale table will always be growing depending on the addition of the upcoming month sale. So, in this INDIRECT Excel example, we want to calculate the sale of the last month
So, here the last month is Mar and we want to calculate the total sale of the March month which is $249,344 using the INDIRECT function
In this INDIRECT Excel example, we will find the use of the second argument of the INDIRECT function
a1: which is an optional argument, it is a logical value (a Boolean value) that specifies what type of reference (ref_text) is contained in the cell
if a1 is true (1) or omitted, ref_text is considered A1-style reference
if a1 is false (0), ref_text is interpreted as an R1C1 style
How is the A1 style is different from R1C1 Style?
Normally, the INDIRECT function in Excel uses A1 notation. Each cell address consists of a column letter and a row number. However, the INDIRECT function in Excel also supports R1C1 notation. In this system, cell A1 is referred to as cell R1C1, cell A2 as R2C1, and so on.
To change to R1C1 notation, choose File-> option to open excel options dialog box, click the formula tab, and a checkmark to R1C1 reference style option. Now, you will notice that the column letters all change to numbers. And all the cell references in your formulas also adjust.
So, when we use the R1C1 style in the INDIRECT function in excel we pass the a1 value as false and if we use A1- style then we pass value true to a1.
Now, in this INDIRECT Excel example, we will be using the R1C1 style due to the requirement as the columns with month would be growing.
Using the R1C1 style, the total sale value is in row 10 and the last column value which is in D column will be calculated using the COUNTA function which will give the last column number containing a value. So, count(10:10) in this case will give 4 which the column number in R1C1 style. So, we have contacted that value to get R10C4 that contains the desired value that is the last month’s total sale value ($249,344).
And, since we are using the R1C1 style here, we will pass a false value for the a1 argument.
Now, let us add one more column with month April Sale
So, get new last month’s sale values corresponding to that of last month of April. Similarly, if we will add more months we will always get new last month’s sale value without changing the indirect excel formula for each month. This is the specialty of INDIRECT function in excel.
How to customize the table array in the INDIRECT Excel VLOOKUP function?
We have five countries and a search channel and the sale that the website made through these different channels from different countries. Now, in cell B3, we want to get the sale that was through Organic searches from country United States.
For each, table for different countries we have named each table with the short name for the country, Ind for India, States for the USA, Can for Canada, and so on.
If we directly use the INDIRECT Excel vlookup function to get the value, as done below
We get an error because the INDIRECT Excel vlookup function is not able to recognize the table array passed as the value of the B2 cell, so we will use the INDIRECT function in excel to make the vlookup get the table_array recognized as a valid argument
If we change the values of B1 and B2, we will get different Sale values in B3, as required.
Note: Name ranges are not case-sensitive. Hence – “Ind”, “IND”, and “InD” are all the same to Excel.
In the next INDIRECT Excel example, we will be using the INDIRECT function in excel to create a dependent list. We have a list of the position of an employee in a company, then for each position, we have the names of the employees
Now, in cell I2 we will be creating a list for different employee ranks as their positions and in I3 we will show a list which will be dependent on the value in I2. If I2 is a director then I3 should reflect the names of director that Andrew and Micheal, if I2 is Manager, then I3 should reflect the names of the manager that is Camille, David, Davis, and William and in the same way for the supervisor.
In this case, again we will use the INDIRECT function in excel to create the dependent list. Using data validation, in settings selecting validation criteria as a list and in source field we will be using the INDIRECT function in excel as follows
When we are selecting director names in I3 reflects as a list with names Andrew and Micheal, when I2 is Manager, then I3 reflects the names of manager Camille, David, Davis, and William and similarly for the supervisor as shown below in the figure
Things to Remember
- The ref_text argument must be a valid cell reference or INDIRECT will return the #REF! error. The ref_text argument can refer to another workbook.
- If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
- If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.
This has been a guide to INDIRECT Excel Function. Here we discuss the INDIRECT Formula in excel and how to use the INDIRECT function in Excel along with practical examples and downloadable excel templates. You may also have a look at these other lookup and reference functions in excel