Using the `INDIRECT`

function in combination with `CONCATENATE`

can be a powerful tool that speeds up work in Google Sheets. But how do these functions work together, and what is a common use case?

To start, let’s explore how each function works individually.

### CONCATENATE

`CONCATENATE`

is a function in Google Sheets that appends multiple strings to one another. For example, if we were to enter in a cell

`=CONCATENATE("Hi, ", "there!")`

we would get `Hi, there!`

as a result. Pretty straightforward!

In our example we’ve concatenated two strings together, but there’s no limit – we can append as many strings as we like with this function.

### INDIRECT

`INDIRECT`

is a function that accepts a string as input and treats that string as if it were a valid cell reference range.

As an example, if we have a range like `names!A1:A100`

, we can

- type that in quotes as a string:
`"names!A1:A100"`

- and put it inside INDIRECT:
`INDIRECT("names!A1:A100")`

The result is that we now have a valid reference to the range `names!A1:A100`

.

Below is a simple example that plots the value found in cell A1. We can see that using the `INDIRECT`

function gives the same result as if we were to use a standard range reference.

### Putting it together

So how do these functions work together optimally? We use the `CONCATENATE`

function to build the string that will be turned into a valid cell reference range by the `INDIRECT`

function.

A common way of applying these functions is referencing **names of spreadsheet tabs**. Let’s walk through an example.

### Referencing names of spreadsheet tabs

Let’s suppose we have a spreadsheet that stores test data for a group of students. Each student’s scores are located on a different sheet:

and there are multiple columns of scores on each student’s tab – one column for each subject. Here is what one student’s tab might look like:

Now, suppose we have a “Totals” tab and we’d like to report average scores for each student. A perfect opportunity to use `INDIRECT`

and `CONCATENATE`

!

Here is our “Totals” tab, along with the formula to compute the average English score for Michael:

The formula is cell B4 is

`=AVERAGE(INDIRECT(CONCATENATE($A4,"!B2:B")))`

but we can see that it’s really equivalent to:

`=AVERAGE(Michael!B2:B)`

Here is how they are equivalent:

- The
`CONCATENATE`

function takes “Michael” and “!B2:B” as inputs, and return the string`"Michael!B2:B"`

. - The
`INDIRECT`

function turns that string into a valid range, and the outer`AVERAGE`

function computes the average of that range!

The combination of these two functions makes it easy to build **dynamic** ranges that reference different sheets.

This means that we can drag the formula in cell B4 **down** so that English averages are computed for all students. No problems at all.

However, if we try to drag cell B4 to the **right**, the formulas will still just return the English average for Michael.

Why is this happening?

The concatenated string uses “B2:B”, and since it is a string the “B” is not converted to “C” as we drag to the right. As a result, we can’t get an average of the ranges `Michael!C2:C`

, `Michael!D2:D`

or `Michael!E2:E`

. We’re stuck getting an average of `Michael!B2:B`

.

Let’s examine how we might alter the formula so that it dynamically updates to use different columns *inside* the `CONCATENATE`

function as formulas are dragged to the right.

We really just want “B” to be changed to “C”, “D”, and “E” as formulas are dragged, so first we need to work out how to do that.

The answer is a combination of the `SUBSTITUTE`

and `ADDRESS`

functions:

`=SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),"1","")`

Here is how that formula works:

`ADDRESS`

takes 3 inputs:*row*,*column*,*mode*.- In this case, we really just care about the
**column**changing as we drag formulas, so any row number will do. Let’s choose “1”. - Since we’re trying to generate a dynamic reference to “B” and column B is the second column, we want to dynamically generate “2”. We can use
`COLUMN(B1)`

to do that. - The mode input of “4” tells Google Sheets to return the cell range in the format “A1”, as opposed to “$A1”, “A$1” or “$A$1”.
**The result of these three inputs is the string “B1”.**

- In this case, we really just care about the

`SUBSTITUTE`

takes 3 inputs as well:*text_to_search*,*search_for*,*replace_with*.- The text we’re searching is the string “B1” returned by the
`ADDRESS`

function - We want to return just the “B” from that string, so we’ll search for the “1”, and choose to remove it by…
- Replacing it with an empty string, i.e. “”
**The result of these three inputs is the string “B”**

- The text we’re searching is the string “B1” returned by the

Whew! That was a pretty complicated formula just to generate a single letter. But, when we plug it in to `INDIRECT`

+ `CONCATENATE`

we get a formula that generates *dynamic* ranges that work when dragging up *or* across a spreadsheet. That’s powerful!

Now, when we plug our inner `SUBSTITUTE`

formula into our original `INDIRECT`

formula, we get the final formula for cell B4:

`=AVERAGE(INDIRECT(CONCATENATE($A4,"!",SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),"1",""),"2:",SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),"1",""))))`

All that’s left is to drag across and down, and we have our table of average scores:

### A word of caution

There is one drawback to the `INDIRECT`

function: it’s a volatile function.

Cells with volatile functions are recalculated whenever *any* calculation occurs in *any* cell of the spreadsheet. This isn’t the case for most Google Sheets functions, but `INDIRECT`

is one of the few functions that is volatile.

By their nature, volatile functions can slow down spreadsheet processing drastically if many cells are using them. So, if you’re using `INDIRECT`

in many hundreds or thousands of cells and you’re referencing ranges that are themselves quite large, the `INDIRECT`

function may not be the best option.

### Summary

The `INDIRECT`

and `CONCATENATE`

functions make a powerful team if applied towards a limited number of calculations. If your data storage model uses multiple spreadsheet tabs that follow similar structures, then you may be able to use these two functions in combination to quickly analyze and report on your data.