Simple Google Sheets Indirect Function Guide (3 Examples)

There is a myriad of functions in Google Sheets, most of which are quite straightforward to understand.

The Google Sheets INDIRECT function, however, is one of those Google Sheets functions that may not be that easy to ‘get’ at first.

To understand the full potential of this function, you need to also have a look at some of the real-world examples (also covered in this tutorial).

What does the INDIRECT function in Google Sheets Do?

In simple words, the INDIRECT formula Google Sheets returns a reference to a given range.

Let me explain with an example:

Suppose you have the value of 5 in cell A1, and install it to use the formula =INDIRECT(“A1”), then it would give you 5 as the result.

Now you might be thinking why do you need to use the INDIRECT formula when you can simply refer to the cell. So instead of using the formula, you can simply use =A1 in cell A2 and it would give you the same result.

You are right!

In this scenario using the INDIRECT function doesn’t make sense.

But there are some cases where you do not have the reference of a cell or Range available readily. In such a scenario indirect function would be very useful.

I am sure the utility of this function would become clearer when I show you some real-world examples (later in this tutorial)

Syntax of the INDIRECT Google Sheets Function

The syntax for the indirect function is as follows:

INDIRECT(ref_text,[ref_style])

The function takes 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 “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 that the ref_text parameter can also contain a reference to a cell that, in turn, contains a string with the reference to another cell.

If this all sounds confusing, it will get clearer with a few examples.

Understanding the INDIRECT Function

Before you learn how to use INDIRECT in Google Sheets, you’ll need to understand how it works.

In the following dataset, we can see 4 different ways of referring to the value in cell A2, out of which one of the ways is incorrect.

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

   

2. In the second row, we used the INDIRECT function to refer to cell A2. Here, we specified the text string “A2” as the ref_text parameter: =INDIRECT(“A2”). Notice that we put the cell reference in double quotes (since it is a text string). This returned the value in cell A2, which is 25.
   

   

3. In the third row, we used the INDIRECT function to refer to cell A2. Here again, we specified A2 as the ref_text, but we did not put it in double-quotes: =INDIRECT(A2). This means we are not passing the string “A2”. Thus, this returned 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). This means we are not passing the string “A5”, rather we are passing a reference to the cell A5. The cell A5 in the dataset contains the string “A2”, which we use as the reference for our INDIRECT function. Thus, this returned the value in cell A2 (not A5), which is 25.
   

   

Google Sheets INDIRECT Function Examples

We hope by now you understand how the INDIRECT function works. Your understanding of this will, however, solidify only after you see a few examples of when to use the function.

So here are some use-cases where the INDIRECT function can prove to be quite helpful.

Using the INDIRECT Function to Lock a Cell Reference

The INDIRECT function provides a great way to ‘lock’ specific cells or Google Sheets INDIRECT range of cells in a formula. To demonstrate, let us take a look at the following example:

Let us assume that the above list of names is a list of winners for a championship, and the list keeps getting updated with the leading champion’s name always entered on top. Which means 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 the cell A2: “=INDIRECT(“A2”)”

Since at the moment John Smith occupies cell A2, both the methods display the same leading champion, “John Smith”.

Now let us assume a new winner, named ‘Samuel Johnson’ is found, and a new row is created in cell A2 to accommodate his name. Note that this causes John Smith’s name to go move to the next row (A3).

Let us see what happens to the leading champion values.

Using the conventional method of cell referencing, we still get the winner as John Smith. This is because the reference to cell A2 now changed to A3 on 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.

You can access this example sheet here.

Using The INDIRECT Function With Multiple Sheets

Another situation where the INDIRECT Function is quite handy is when you have to pull in data from two sheets or multiple other sheets. The indirect function is able to use strings across multiple sheets.

Let’s take another example.

Suppose you have three sheets, named ‘Red’, ‘Orange’, and ‘Blue’ (as shown below):

In the fourth sheet, we want to display the value in cell B2 from each of the first three sheets:

Conventionally, you would have had to type the name of each sheet along with the cell reference B2, as follows:

=SheetName!B2

Imagine doing that when you have 30 worksheets!

An easier way would be 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 gets copied to all the cells of column B

You now have the value of cell B2 from each sheet displayed in column B!

This makes the work a whole lot easier than the conventional method.

You can access this example sheet here.

Explanation of the Formula

When we clicked the fill handle, the cell reference before the ‘&’ in each cell got updated to the next Google Sheets indirect sheet name.

So,

• 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

In this way, with just a double-click, we got all the cells of row B updated with the required data.

Note: If you need to pull data from different cells in the different sheets, you can have a separate column containing the cell references for each sheet, and then use the INDIRECT function as follows:

Using INDIRECT Function to Dynamically Refer to a Named Range

This is a great application of the INDIRECT function.

Say you have three columns for three colors as shown below.

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 that is displayed in cell C11 in the above dataset will vary depending on the selected color.

Note that it is not possible to know in advance which color will be selected. So 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. Now try selecting a color from the drop-down list. You will see the sum getting updated accordingly in cell C11.

You can access this example sheet here.

Explanation of the Formula

Let us break down the formula we used:

=SUM(INDIRECT(C6))

1. Here, the INDIRECT function takes the string which is in cell C6 as its parameter. So 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 which are in the Google Sheets indirect named range selected and return the sum.

In this way, with just a small, completely simple formula, we could dynamically obtain the sum we need. Otherwise, we would have had to resort to complex formulas involving a number of nested IF functions.

Common Errors with The INDIRECT Formula in Google Sheets

• Not using quotation marks around the cell reference – Check you’ve written “A2” instead of (A2) for example
• Using invalid cell references – If you’re using the A1 cell address notation for cell_reference_as_string  and TRUE as  is_A1_notation will mean the formula is looking for the wrong type of notation.

Frequently Asked Questions

Is Indirect Volatile in Google Sheets?

Yes, the Indirect function in Google Sheets is volatile. What this means is that it refreshes each time you edit a cell or everytime there is a change in the worksheet.

Can I Use the Indirect Function in VLOOKUP?

Yes, you can combine the VLOOKUP function with the indirect function Google Sheets. This is also another way to use the Indirect function with multiple sheets since these two functions together let you 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 sheets Red, Orange, Blue, and Consolidated sheet.

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

2. Then we can create a Vlookup formula with an Indirect formula nested in it. The VLOOKUP will look for any data that matches cell A2, which is Pierre Cox.

3. Next, we add the indirect formula, which is the range from which to look up the data from the multiple sheets of red, orange and blue.

Since the name 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. Finally we need to add the number, which is the column ID for the data we want, and 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 lookup 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 to go through the INDIRECT as the rows to account for.

What Does Indirect Do in Google Sheets?

in a nutshell, 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. But 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? ( Can Google Sheets INDIRECT Another Sheet?)

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.For example:

=INDIRECT (“Sheet 2″&”!”&”A2″)

This would give you results based on data from Sheet 2.

How Do You Do INDIRECT Validation in Google Sheets?

Data validation works perfectly with the INDIRECT function to create spreadsheet features such as dropdown lists. We have a full guide on that here. It breaks down this complex process in an easy-to-understand way.

The INDIRECT Function Has Many More Uses

There are a number of other ways in which the INDIRECT function can be applied. In this tutorial, we explained the syntax of the INDIRECT function, along with some formula examples.

To help make your understanding clearer, we provided a few basic use-cases where this function can be applied.

We encourage you to find more creative ways to apply the Google Sheet INDIRECT function and make your work easier.

I hope you found this tutorial useful.

Other Google Sheets tutorials you may like:

• • 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 (Examples)
  • IFERROR Google Sheets