December 4, 2020

INDIRECT function in Google Sheet

The INDIRECT function returns a valid reference  to a cell based on a given text string in both Excel & Google Sheet. INDIRECT function is used to indirectly reference cells, ranges, other sheets or other Google Sheet file.The INDIRECT Function returns a reference to a range. The INDIRECT function does not perform any calculations or evaluate any logical conditions. With the use of this function, we can change a cell reference in a formula without changing the formula itself.

One other advantage of this function is that the indirect references won’t change even when rows/columns are added or deleted in the worksheet.

=INDIRECT(ref_text, [a1])

Arguments :

  • ref_text – A reference supplied as text. Make sure this argument doesn’t refer to a range of cells beyond the row limit of 1,048,576 or the column limit of 16,384, otherwise you will get the #REF error in Excel 2007, 2010 and Excel 2013.
  • a1 – [optional] A boolean to indicate what type of reference is contained in the ref_text argument
      • If TRUE or omitted, ref_text is interpreted as an A1-style cell reference as default.
      • If FALSE, ref_text is considered as a R1C1 reference.

In the below example we have used INDIRECT function to fetch the value in cell B4. But in ref_text argument of INDIRECT function, the cell E2 has been mentioned. In cell E2, the cell reference for B4 has been specified so the INDIRECT function is fetching the value 15 from cell B4. This technique is used to lock a cell reference.

Now let’s take another example. Here we have added the cell B7 directly in ref_text argument of INDIRECT function within double quotes (“”).

Now let’s take another example of INDIRECT function. Here in ref_text argument we provided “B”&D2, which means the value should be returned from B column and the row number as mentioned in cell D2. Here in cell D2 the row number specified as 5 so the value fetched by INDIRECT function is from cell B5.

Below is another example of INDIRECT function used in combination with other function as SUM. Now this INDIRECT function fetches the values from cell B5:B9 so the formula finally evaluates as SUM(B5:B9).

Now we will see how to refer a named range in an worksheet using INDIRECT function. Below is our next example containing the marks in different subjects for several students in a college batch.

First to create the named range we need to name each column as follows.

B2:B10 – Economics

C2:C10 – Computer

D2:D10 – Sociology

To provide a name to a range of cells you need to select the cell range and then click Data in the menu bar of the Google Sheet. Then Go to Named ranges.

Now in the Named ranges pane provide the desired name for the selected cells of the column. Then click Done.

Now we need to find the highest marks in each named range. The named range has been mentioned in the cell G2:G4. The highest marks in each named range has been fetched by MAX and INDIRECT function in cells H2:H4 as follows.

In the next example we will learn to create a dependent drop down list with the help of INDIRECT function in excel. Here we have the below table containing list of some bird and animal species.

Here also we have created the named range as follows.

A2:A6 – Bird

B2:B6 – Animal

Now in cell D2 first create the Drop down to select the category as Bird/Animal. 

  1. To Do this first select cell D2.
  2. Then Go to Data menu of the Google Sheet and click Data Validation

3. In Data Validation Pane select the Criteria as ‘List from a Range’ and then we have added the cell range as A1:B1. Then click Save.

4. The category Drop Down list has been created in cell D2 with the values as Bird and Animal as mentioned in the source cell A1:B1. Now we will create the dependent dropdown list in cell E2. It means the drop down list should be dynamically created based on the value selected in drop down list D2.

5. To do that first enter the below function anywhere in the current Google spreadsheet. Here we have added the formula in cell A9.

=INDIRECT(D2)

6. Now based on the category selected in the drop down of cell D2, it will fetch the list from the respective column of the source table. As in the below pictures we have shown the list of Birds or Animals will be populated starting from cell A9 depending on the selection of category as Bird/Animal in cell D2.

7. Now to create dependent drop down in cell E2 first select the cell E2. Go to Data Validation from Data menu. In Data Validation pane select the criteria as ‘List from a range’ and in the cell range we have specified A9:A15(included 2 additional cells to make room in advance for the probable future increment of the list of birds/animals). Now click Save.

Remember, if you add more birds or animals in the source table, the named range also needs to be updated to include the additional cells of the respective columns.

8. Now as you can see below, the dependent drop down list is working as expected in cell E2. Upon selecting Bird in cell D2, the drop down in cell E2 is populated with the list of birds and the same happens for Animal. As you can understand here the drop down list is being fetched from the cell range A9:A15 as specified above.

Here is another example to find the number of mentioned days between 2 given dates using INDIRECT function. See below we have a table containing 2 specific sets of start date and end date.

Here in column C we mentioned the day number in a week considering Sunday as 1. Let’s say for the first row we need to count the number of Wednesdays(4th day of the week is Wednesday) between 2nd Jan to 22nd Jan. Now here we have used the below formula.

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=C2))

Let’s break the formula for step by step understanding.

ROW(INDIRECT(A2&”:”&B2))  – This part returns the array of concatenated dates between the specified start date and end date.

WEEKDAY function evaluates the numbers in the array as dates and returns the day number in the week which is being matched with the given day of the week as mentioned in cell C2 and converted to 1s and 0s with the double hyphen.

Finally SUMPRODUCT function counts the 1s and results the final count of the occurrences of the specific day between the given date range.

Here is the last example of INDIRECT function with the second argument as FALSE. So far we omitted the second argument in all of the above examples it means the default style of ref_text is treated as regular A1 style cell reference which is first column and then row reference.

Here the second argument is FALSE which means it will treat the value in ref_text as in R1C1 style that is first row and then column reference. Now in ref_text we mentioned D2. In cell D2 we added R10C2, means it will fetch the value from 10th row and 2nd column(which is cell B10 actually).

Note : 
  • When ref_text is an external reference to another workbook, the workbook must be open while using this function.

Leave a Reply

Your email address will not be published. Required fields are marked *