Have you ever wondered how to convert text strings into valid cell or range references in Google Sheets? Well, look no further because the INDIRECT function is here to save the day!
What is the INDIRECT function?
The INDIRECT function in Google Sheets is used to convert text strings into valid cell or range references. For example, by using the INDIRECT function, you can convert the text string “A1” into the cell reference A1. The formula looks like this:
=INDIRECT("A1")This is equivalent to the following formula:
=A1By using the INDIRECT function, you can perform calculations or retrieve values based on the converted cell or range references.
Syntax of the INDIRECT function
The INDIRECT function takes two arguments:
- cell_reference_as_string – This is a text string that represents a cell or range reference. For example:
=INDIRECT("A1")This can also point to a cell that contains a range reference, as shown here:
The value in cell B1 is the text value “A1”. When the INDIRECT function points to B1, it retrieves this string reference and converts it into an actual cell reference.
-
[is_A1_notation] – This is an optional argument that can be either TRUE or FALSE.
- If you set it to TRUE, the INDIRECT function will use the familiar A1 notation (e.g., A1, B2:D15, G1:M1000).
- If you set it to FALSE, the INDIRECT function will use the unusual R1C1 notation (see below).
If you omit this argument and your INDIRECT has only one argument like the example at the top of this page, it defaults to using the A1 notation as if you had used a TRUE argument.
I have never seen a formula with this second argument set to FALSE in practice, so you can definitely ignore this argument and use the INDIRECT function with a single input.
Note on R1C1 notation
The R1C1 notation describes a cell or range reference using a row number and a column number.
R1C1 refers to an absolute reference to the cell at position (1,1) in your sheet, which is A1 in normal notation.
The bracketed reference – R[1]C[1] – is a relative R1C1 reference, which means the cell is located 1 row below and 1 column to the right of the current cell, wherever it may be in your spreadsheet.
Here’s an example of the INDIRECT function using R1C1 notation:
Both formulas have FALSE as the final argument of the INDIRECT function, so they use the R1C1 notation.
The first formula has absolute notation – “R1C1” – so it points to the cell A1, the cell in row 1 and column 1 of your sheet.
The second – “R[-1]C[0]” – is a relative reference instructing the INDIRECT function to go up one row (from row 2 to row 1) and stay in the same column (as the C value is 0).
This is equivalent to the formula:
=R[-1]CR1C1 notation can also refer to ranges by joining two cell references with a double colon “:”, similar to how ranges are referenced in A1 notation. For example:
=R1C1:R5C3Using the INDIRECT function to work with multiple sheets
Let’s say you have multiple sheets in your Google Sheets file, each with a similar structure. For example, you might have transaction data by year, each in its own sheet: Data 2018, Data 2019, Data 2020, Data 2021.
If you want to perform a calculation across all four sheets (e.g., a grand total), you would typically have to click on each sheet from within a formula, which can be quite tedious, especially if you have many sheets.
By using the INDIRECT function, you can build the formula with text strings to avoid having to click on each tab.
With a list of sheet names in column A, you can use this INDIRECT formula in column B to access the values from each sheet without having to click on each tab:
=INDIRECT("'"&A1&"'!B6")Drag this formula down the column, and it will return the value from cell B6 for each sheet:
The first formula in cell B2 retrieves the value from cell B6 in the sheet “Data 2018”:
Combining VLOOKUP and INDIRECT in Google Sheets
Here’s another example of using the INDIRECT function to work with multiple sheets.
By combining the VLOOKUP function with the INDIRECT function, you can look up data in different sheets based on user input.
This requires you to have consistently named tabs in your Google sheet, such as Data 2018, Data 2019, Data 2020, Data 2021.
Create a dropdown menu in Google Sheets to allow the user to select one of these sheet names, and then use the INDIRECT function to convert the text string into a valid range reference, which becomes your lookup table:
Using the INDIRECT function to create vectors
NOTE: These methods have largely been replaced by the simpler and more elegant SEQUENCE function, which can easily generate row or column vectors. However, you may still come across the INDIRECT version in online forums or older resources, which is why I am sharing it here.
As you advance in your spreadsheet journey, you will find yourself working with data arrays more frequently than with individual values. You will often need to create a numbered list.
For example, you might want to sort data on the fly but have a way to revert back to the original order. By combining a column vector and array literals {…}, you can build this within the formula without adding a helper column to your data.
Vectors also appear in other complex formulas, such as this text reverser, this flattenizer, or even advanced sparklines like this clock or this value visualization experiment.
The INDIRECT function can be combined with the ROW function to create column vectors or with the COLUMN function to create row vectors (yes, I understand that sounds strange, but you’ll see what I mean below).
This formula creates a column vector from 1 to 10 by descending down a column:
=INDIRECT("A"&ROW(A1:A10))This formula creates a row vector from 1 to 10 by traversing across a row:
=INDIRECT("R1C"&COLUMN(A1:J1)&":R1C"&COLUMN(A1:J1)+9)
The formula can be generalized to create vectors of variable length. One example could be measuring the length of a text string in cell A1 and creating a corresponding vector based on that length.
This particular technique is part of a formula for reversing text in Google Sheets (again, the new SEQUENCE function could make this more concise).
Other Advanced INDIRECT Techniques
The INDIRECT function can also be used to create dynamic named ranges in Google Sheets.
Read all about dynamic named ranges in Google Sheets here.
For further information, you can also refer to the Google documentation.
There you have it! You now have a better understanding of the INDIRECT function in Google Sheets. Have fun exploring and using it in your own projects! For more articles and tips on Google Sheets, visit Crawlan.com.
