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 the 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 the INDIRECT Function in Excel?
Let’s take an INDIRECT excel example before using the address function in the ExcelAddress Function In The ExcelThe address function finds the cell's address and returns an absolute value. It requires two mandatory arguments: the row number and the column number. For example, if we use =Address(1,2), the result will be $B$1. 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 the 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 the 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, it created a named range in excelNamed Range In ExcelName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window. 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 a 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 names from Canada to India, the 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 different sheets with the country names for the totals number of sales made with each search channel for different countries.
For the USA,
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 names 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 the 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’s 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 an A1-style reference
if a1 is false (0), ref_text is interpreted as an R1C1 style
How is the A1 style is different from the 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 the 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 the 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 the 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 the month of April Sale.
So, get new last month’s sales values corresponding to that of the 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 a 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 directors 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. Data validation, in settings selecting validation criteria as a list and in the 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