Google Sheets INDIRECT Function Guide (3 Examples)

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!

What Does the Google Sheets INDIRECT Function Do?

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.

Syntax for the Google Sheets INDIRECT Function

The syntax for the Google Sheets INDIRECT function is as follows:

INDIRECT(ref_text,[ref_style])

The function has two parameters:

• ref_text: This is a text string containing a reference to a cell or range of cells
• ref_style: This parameter is optional. It is specified with a TRUE/ FALSE value. A TRUE value indicates that the ref_text is specified in the “A1” notation, while a FALSE value indicates that it is specified in the “R1C1” notation. If this value is not specified, the default value is assumed to be TRUE.

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.

Understanding the Google Sheets INDIRECT Function

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.

1. In the first row, we used the regular method of referring to a cell reference (the A1 notation): =A2
   • This returned the value in cell C2 of 25
     

     

2. In the second row, we used the Google Sheets INDIRECT function to refer to cell A2
   • Here, we specified the text string “A2” as the ref_text parameter: =INDIRECT(“A2”)
   • The cell reference is in double quotes (since it is a text string), returning the value in cell C3 of 25
     

     

3. In the third row, we used the Google Sheets INDIRECT function to refer to cell A2.
   • Again, we specified A2 as the ref_text, but we did not use double-quotes: =INDIRECT(A2)
   • Therefore, we are not passing the string “A2,” resulting in a #REF error
     

     

4. In the fourth row, we used the INDIRECT function to refer to cell A5.
   • Here, we specified A5 as the ref_text parameters, without double quotes: =INDIRECT(A5)
   • We are not using the string “A5,” but we use a reference to the cell A5
   • Therefore, the cell A5 contains the string “A2,” which we use as the reference for our INDIRECT function
   • This returned the value in cell C5 of 25
     

     

Google Sheets INDIRECT Function Examples

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.

Access Sheet

Using the INDIRECT Function to Lock a Cell Reference

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.

Using The INDIRECT Function With Multiple Sheets

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.

Access Sheet

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:

1. In cell B2 of the new sheet, type the formula:

   =INDIRECT(A2&"!B2")

2. Press the “Return” key
3. Double-click the fill handle of cell B2
4. The formula will be copied to all the cells in column B

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.

• In cell B2, the function was =INDIRECT(A2&!”!B2”), which refers to cell Red!B2
• In cell B3, the function was =INDIRECT(A3&!”!B2”), which refers to cell Orange!B2
• In cell B4, the function was =INDIRECT(A4&!”!B2”), which refers to cell Blue!B2

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:

Using the Google Sheets INDIRECT Function to Refer to a Dynamic Named Range

This is a great application of the INDIRECT function. Download the example spreadsheet to help you follow along.

Access Sheet

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:

1. Create a named range for each of the colors. Start with “Red“
   • To create a named range for the Red values, select the cells under “Red“
   • Select “Data” > “Named Range“
   • This will open the “Named Ranges“ sidebar on the right side of the window.
   • Type “Red” in the input box above the cell range.
     

     

   • Click “Done“
   • This will create a named range for the group of cells under “Red”
     

     

2. Create named ranges for each color in the same way
   

   

3. Select cell “C11“
4. Type the formula:

   =SUM(INDIRECT(C6))

5. Press the “Return” key
6. Select a color from the drop-down list. You will see the updated “sum“ accordingly

You can access this example sheet here.

Explanation of the Formula

Let us break down the formula:

=SUM(INDIRECT(C6))

1. Here, the INDIRECT function used the same string in cell C6 as its parameter
   • If the text “Red” is selected, the INDIRECT function will return a reference to the named range, “Red”
2. The SUM function will take the numeric values that are in the selected Google Sheets INDIRECT named range and return the sum

Using this method, we can obtain the sum we need, rather than resorting to complex formulas that involve nesting IF functions.

Common Errors with The INDIRECT Formula 

• Omitting quotation marks around the cell reference: Always check you’ve written “A2” instead of (A2)
• Using invalid cell references: Make sure you’re using the A1 cell address notation to avoid the wrong type of notation

Frequently Asked Questions

Is the Google Sheets INDIRECT Function Volatile?

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.

Can I Use the INDIRECT Function in VLOOKUP?

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.

1. The first step would be to create a drop-down menu for the color labels using data validation:

2. Create a VLOOKUP formula with an INDIRECT formula nested in it
   • The VLOOKUP will look for any data that matches cell A2 (i.e., Pierre Cox)

3. Add the INDIRECT formula, which is the range to look up the data from the multiple sheets of red, orange, and blue.

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.

4. Add the number, which is the column ID for the data
   • Either 0 or FALSE to give an exact match

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.

How Can I Use the Google Sheets INDIRECT Function With MATCH?

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.

What Does INDIRECT Do in Google Sheets?

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.

Can I Use The INDIRECT Function With Sheet Tabs? 

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.

How Do You Do INDIRECT Validation?

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.

Wrapping Up

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: 

• • How To VLOOKUP from Another Sheet in Google Sheets?
  • How To Use SUMIF function in Google Sheets? Examples!
  • How To Use IMPORTRANGE Function in Google Sheets
  • How To Use the FILTER Function in Google Sheets
  • How To Use SUMIFS Function in Google Sheets
  • IFERROR Google Sheets