The INDIRECT function in Google Sheets displays a value stored in another cell using its corresponding absolute reference. It can be combined with other Google Sheets functions. Learn more how to use this formula in Google Sheets, with a wide variety of examples and formulas shown below and a list of common errors you may encounter while using this handy formula.
At its simplest, the function has the following syntax:
=INDIRECT(reference, A1_notation)
Where
reference contains the reference that you want to use. You need to enclose it in double quotes; else Google Sheets will interpret this as pointing to another cell containing the reference.
A1_notation tells Google Sheets the syntax of the reference. This is optional and is set to TRUE by default.
The INDIRECT function can be used in conjunction with other functions to convert their output to actual reference and input the values stored in the cell being referenced.
Google Sheets uses two reference notation systems: A1 notation and R1C1 notation.
A1 notation is the one used by default - columns are labeled using letters starting from A while rows are labeled using numbers starting from 1. The first cell of the sheet is thus marked A1.
The R1C1 notation uses numbers for both rows marked R and columns marked C. The first cell of the sheet is thus marked R1C1.
We are more accustomed to using the A1 notation as it is the one we can easily see in Google Sheets interface: the columns are labeled in letters while the rows are labeled in numbers. However, the R1C1 notation has its own benefits, and you may find it more useful when making a dashboard for example.
If you are someone who learns best through examples, this section will be a gold mine of examples that will help you learn how to use the INDIRECT function.
You can place the reference directly inside the INDIRECT function, enclosed in double quotes:
=INDIRECT(“reference”)
Where reference is the reference to the cell you want to call.
Adding the TRUE value for the A1 notation option is optional, but you can still do so to ensure your formula works perfectly:
=INDIRECT(“reference”, TRUE)
Where reference is the reference to the cell you want to call.
If you want to use R1C1 notation inside the formula, make sure to set A1_notation to FALSE:
=INDIRECT(“reference”, FALSE)
Where reference is the reference to the cell you want to call, this time in R1C1 notation. In one of the succeeding examples, you will be able to see the benefit of using R1C1 notation instead of the more common A1 notation.
If you add a reference in the formula without enclosing it in double quotes, Google Sheets will read the value stored in that cell and attempt processing it as a reference:
=INDIRECT(reference_storage)
Where reference_storage is the reference to the cell where the reference you want to process using the INDIRECT function is stored.
It is the same as Example 4 above, but the reference has dollar signs, also known as an absolute reference. Dollar signs are added to references in Google Sheets to keep the reference fixed even if the formula using it is copied to other adjacent cells.
=INDIRECT(reference_storage)
Where reference_storage is the reference to the cell where the reference you want to process using the INDIRECT function is stored.
It is the same formula as Example 4 above, but the reference is in R1C1 notation. You just need to set A1_notation to FALSE:
=INDIRECT(reference_storage, FALSE)
Where reference_storage is the reference to the cell where the reference you want to process using the INDIRECT function is stored.
If the column letter and the row number are stored in separate cells, you can combine them and then input it in the INDIRECT function. Use the following formula syntax:
=INDIRECT(column&row)
Where
column is where the column letter is stored
row is where the row number is stored
The ampersand & combines the two strings together inside.
You can also use CONCATENATE:
=INDIRECT(CONCATENATE(column,row))
Where
column is where the column letter is stored
row is where the row number is stored
If the column number (instead of column letter) and the row number are stored in separate cells, you can combine them into R1C1 notation and then input it in the INDIRECT function. You have two ways of doing it. The formula for the first way is:
=INDIRECT(“R”&row&”C”&column)
Where
column is where the column letter is stored
row is where the row number is stored
The ampersand & combines the strings together inside.
For the second method, you can also use CONCATENATE:
=INDIRECT(CONCATENATE(“R”,row,”C”,column))
Where
column is where the column letter is stored
row is where the row number is stored
The INDIRECT function can process references that point to another sheet. Just add the sheet name just like how you reference ranges from a different sheet, using the same formulas as shown in the previous examples:
The INDIRECT function can also process the reference that is stored in a cell in another sheet, whether it is stored in whole or in parts. Note that to ensure the formula works well, either store the reference as a whole or consolidate it using CONCATENATE inside the INDIRECT formula:
=INDIRECT(CONCATENATE(address_to_merge))
Where address_to_merge is the set of strings to merge into an address to point to a cell.
You can use named ranges as references in your formula:
=INDIRECT(named_range)
Where named_range is the named range you want to input in double quotes or is the reference to where the named range is stored.
Since named ranges are often used to refer to entire ranges of data, the output is a range containing the data:
Finally, since named ranges are universal within the spreadsheet, you can use it to refer to the cells from other sheets without the complexity present in handling references to other sheets.
You can use ranges as references in your formula:
=INDIRECT(range)
Where range is the range you want to input in double quotes or is the reference to where the range is stored.
You can also combine references stored separately into a range as input:
=INDIRECT(first_cell&”;”&last_cell)
Where
first_cell is the first in the range
last_cell is the last in the range
Or combine the column letters and row numbers stored separately in this formula:
=indirect(concatenate(first_column,first_row":",last_column,last_row))
Where
first_column and last_column are in column letters
first_row and last_row are in row numbers
The syntaxes highlighted for the formulas above will work with R1C1 notation as well, but with few adjustments including setting A1_notation to FALSE.
You can combine with the MATCH formula to create a search formula that gives you a range of results. For example, you may want a formula where you can get the range of results for a given set of bounds. This set of bounds usually comes in the form of dates:
In our example above, we want to find the winning numbers from October 2 to October 5. We use the MATCH function to find the position of the bounds in the range, and then use it to set the reference to the range that will be referenced by the formula:
=INDIRECT("value_column" & MATCH(first_entry,label_range,FALSE) & ":value_column" & MATCH(last_entry,label_range,FALSE), TRUE)
where
First_entry and last_entry are for the bounds of the range you are looking for
Label_range is the range where the labels for the values are stored
Value_column is the column where the values you are looking for are stored
You can combine with ADDRESS to create a search formula whose input are the column and row header:
=INDIRECT(ADDRESS(MATCH(column_label,column_label_range),MATCH(row_label,row_label_range)))
Where
column_label is the column header you are looking for
column_label_range is where the column headers are located
row_label is the row header you are looking for
row_label_range is where the row headers are located
This is a good formula you can use to search for certain entries because it uses the column and row headers, which is the natural way for us to look for values in the tables.
Here are some problems you may encounter with your formula, including a guide how you can fix them.
While the default notation used is the A1 notation, you may sometimes find the R1C1 notation useful. If you are not careful, you can get the #REF error for not setting the formula to use R1C1 notation:
For this error, simply add FALSE to the formula. You can check Example 3 for the steps.
If the input in the string is not even a valid reference in either A1 or R1C1 notation, the formula will also throw a #REF! Error. To solve this problem, edit the function or the value in the cell that contains the reference to include the actual reference.
If you accidentally mistyped the reference in the formula, you may get a blank output. This is because the mistyped reference may point to a cell that may actually exist in the sheet, but since it does not contain any value it produces a blank result. To solve this problem, edit the function or the value in the cell that contains the reference to include the actual reference.
If you use named ranges, chances are the size of the range referenced may overlap with the adjacent cells that already contain values. #REF! error will appear.
If you intend to refer to the named range, you will need to adjust your sheet or the size of the named range, plus adjusting the location of the formulas in your sheet.
We hope this article has helped you and given you a better understanding of how to use the Google Sheets INDIRECT function. You might also like our articles on how to freeze a cell in Google Sheets and how to use IMPORTHTML in google sheets.
You can also automatically send an email from Google Sheets. Find out more!
