How to Use INDIRECT with CONCATENATE in Google Sheets

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

  1. type that in quotes as a string: "names!A1:A100"
  2. 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:

  1. The CONCATENATE function takes “Michael” and “!B2:B” as inputs, and return the string "Michael!B2:B".
  2. 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”.
  • 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”

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.

Leave a Reply

Your email address will not be published. Required fields are marked *