In this article:

May 9, 2024

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!

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->