Have you come across the term, “Google Sheets INDIRECT,” but you’re unsure what it means?
While Google Sheets has a myriad of functions that are pretty straightforward, the Google Sheets INDIRECT function, is, however, one that is more difficult to understand.
Therefore, to understand the full potential of this function, we will look at several examples in this tutorial. Let’s get started!
Table of Contents
The Google Sheets INDIRECT formula returns a reference to a given range.
For example, if you have the value of 5 in cell A1. You can use the following formula to give you the same value of 5 in cell A2.
=INDIRECT("A1")
Here, you can use =A1 in cell A2 to get the same result.
If you do not have the cell reference or range readily available, then using the Google Sheets INDIRECT function is very useful.
The syntax for the Google Sheets INDIRECT function is as follows:
INDIRECT(ref_text,[ref_style])
The function has two parameters:
Note: The ref_text parameter may also contain a reference to a cell that, in turn, contains a string with reference to another cell.
Confused? Don’t worry, let’s look at a couple of examples to give you a better idea.
Before you learn how to use the Google Sheets INDIRECT function, first, you need to understand how it works.
In the dataset below, there are four ways of referring to the value in cell A2. Out of the four values, one of which is incorrect.
To solidify your understanding of the Google Sheets INDIRECT function, here are a few more examples of when and how to use the function.
We have also included a helpful example, which you can access to help you follow the examples better.
The Google Sheets INDIRECT function provides a great way to ‘lock’ specific cells or a range of cells in a formula. Let us take a look at the following example:
Let us assume that the list of names above is a list of winners for a championship, and the list keeps getting updated with the leading champion’s name being entered at the top. This means that whoever’s name is in cell A2, is the current leading champion.
Let us create a reference to this cell both with and without the INDIRECT function. In the image below, we created a reference to cell A2 using the conventional cell reference “=A2” in cell B9.
In cell B10, however, we used the INDIRECT function to refer to cell A2 using the following formula:
=INDIRECT("A2")"
In the example above John Smith occupies cells B9 and B10, indicating he is the leading champion for both methods (without INDIRECT and with INDIRECT).
Now let us assume a new champion, ‘Samuel Johnson’ has won, and a new row is created in cell A2 to accommodate his name. Note: This moves ‘John Smith’s’ name to the next row (A3).
Using the conventional method of cell referencing, we still get the winner, ‘John Smith.’ This is because the reference to cell A2 has changed to A3, adding a new row.
Using the INDIRECT function, however, the champion’s value is updated to Samuel Johnson, since the reference string in the function does not change even after the addition of a new row.
As you can see, the INDIRECT function helps ‘lock’ cell references. As such, it is very useful when you want a value to refer to a fixed cell location, rather than rely on the value in the cell location.
Another situation where the INDIRECT function is handy is when you pull data from two sheets or multiple other sheets. Here, the Google Sheets INDIRECT function can use strings across multiple sheets.
Let’s look at another example. To help you understand better, we have also included an example spreadsheet with the same data set, so make sure you download your copy as you follow along.
If you have three sheets, named ‘Red’, ‘Orange’, and ‘Blue’ (as shown below):
In the fourth (consolidated) sheet, we want to display the value in cell B2 from each of the first three sheets:
Conventionally, you have to type the name of each sheet along with the cell reference B2, which is both inefficient and time-consuming:
=SheetName!B2
An easier way is as follows:
=INDIRECT(A2&"!B2")
You now have the value of cell B2 from each sheet displayed in column B, simplifying the process, especially when compared with the conventional method.
Explanation of the Formula
When we clicked the fill handle, the cell reference before the and symbol (&) in each cell got updated to the next Google Sheets INDIRECT sheet name.
With just a double-click, we have all the cells of row B updated with the required data.
Note: If you need to pull data from different cells or sheets, you can have a separate column containing the cell references for each sheet, and then use the INDIRECT function as follows:
This is a great application of the INDIRECT function. Download the example spreadsheet to help you follow along.
For example, if you have three columns for three colors, as shown below.
And you want to display the total for a color, depending on the column name selected from the drop-down list in C6. This means the total displayed will vary depending on the selected color.
Note: It is not possible to know in advance which color will be selected. To get the right total each time, you need to do the following:
=SUM(INDIRECT(C6))
Explanation of the Formula
Let us break down the formula:
=SUM(INDIRECT(C6))
Using this method, we can obtain the sum we need, rather than resorting to complex formulas that involve nesting IF functions.
Yes, the Goole Sheets INDIRECT function is volatile. This means that it refreshes every time you edit a cell or every time there is a change in the worksheet.
Yes, you can combine the VLOOKUP function with the Google Sheets INDIRECT function. This is also another way to use the INDIRECT function with multiple sheets since these two functions allow you to use LOOKUP data from multiple sheets based on what you specify.
For example, if we were to use our previous example for multiple sheets, we could create a new sheet that looks like this:
At the bottom of the sheet, you can see the other “Red, Orange, Blue, and Consolidated” sheets.
Since the names of the sheets are in cell B2, we can use the cell reference instead of the actual name of the cell:
INDIRECT(B2&"!A1:B2")
This is so that when you select a different color from the drop-down menu, it will return the value from the correct sheet.
The final VLOOKUP formula should look like this:
=VLOOKUP(A2,INDIRECT(B2&"!A1:B2"),2,false)
This formula will look at and return the values from the multiple sheets based on which sheet you select in the color drop-down menu.
You can use the MATCH function and the INDIRECT function combined to create a Google Sheets dynamic cell reference formula.
The MATCH formula finds the data within the specified call range. Then, it goes through the INDIRECT as the rows.
Instead of making calculations based on the direct input of a cell, you can use INDIRECT to calculate with the reference of a cell instead.
It doesn’t make sense to use INDIRECT in simple calculations. However, it can be useful to refer to other ranges and/or dynamically changing fields in a spreadsheet.
Yes, you can use sheet tabs with the INDIRECT function. You just have to use the “Sheet name” as part of the ref_text in the syntax:
=INDIRECT ("Sheet 2"&"!"&"A2")
This will give you results based on the data from Sheet 2.
Data validation works perfectly with the INDIRECT function to create spreadsheet features, such as drop-down lists. We have a full guide on that here. It breaks down this complex process in an easy-to-understand way.
So there you have it, all three ways to use the Google Sheets INDIRECT function. We hope that this tutorial, along with the syntax formulas, and examples has been helpful to make your understanding clearer.
We encourage you to find more creative ways to apply the Google Sheets INDIRECT function and make your work easier. Feel free to leave your comments below if you want to share your insights!
Related:
