How to Use the INDIRECT function in Google Sheets

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

The 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 do?

In simple words, the INDIRECT function 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.

INDIRECT Function Example

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 formula 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 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

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.

ways to refer in Indirect function

  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.Regular method of refercing a cell
  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.Using reference within indirect
  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.INDIRECT function giving an 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.Referring to cell that has the reference

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 INDIRECT Function to Lock a Cell Reference

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

Dataset for INDIRECT formula

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.

Using cell reference

 

In cell B10, however, we used the INDIRECT function to refer to the cell A2: “=INDIRECT(“A2”)”

Using indirect formula to refer to a cell

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).

New row is created

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.

Adding a row changed the cell reference

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.

INDIRECT function continues to refer to the same cell

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 INDIRECT Function to Refer to a Cell in a Different Sheet

Another situation where the INDIRECT Function is quite handy is when you have to pull in data from multiple other sheets.

Let’s take another example.

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

Red Sheet

Orange Sheet

Blue Sheet

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

Consolidated sheet

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")

    Formula to refer to sheet name and cell reference

  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

Final result indirect multiple sheets

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.

Explanation of the Formula

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

Fill handle updated the 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 formula as follows:

Pull data using cell reference as the value

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.

Three color columns

You want to display the total for a color, depending on the column name selected from the drop-down list in C6.

Total for the selected color

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.Type Red in the Named Range box
    • Click Done
    • This will create a named range for the group of cells under ‘Red’.Named Range Red
  2. Create named ranges for each color in the same way.Create all named ranges
  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.SUM with INDIRECT

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 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.

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 INDIRECT function in Google Sheets and make your work easier.

I hope you found this tutorial useful.

Other Google sheets tutorials you may like:

Leave a Comment