Utilizing the INDIRECT Function in Google Sheets [2024 Update]

Hey there, my besties! Are you ready to take your Google Sheets game to the next level? Today, we’re diving deep into the world of the INDIRECT function, a powerful tool that allows you to display values from other cells using their corresponding absolute reference. With the INDIRECT function, you can create dynamic formulas and unlock the full potential of Google Sheets. So, let’s get started!

Syntax of the INDIRECT Function in Google Sheets

To begin, let’s take a look at the syntax of the INDIRECT function:

=INDIRECT(reference, A1_notation)

Here’s what each parameter represents:

• reference is the reference you want to use. Be sure to enclose it in double quotation marks to avoid any confusion.
• A1_notation (optional) indicates the syntax of the reference. By default, it’s set to TRUE.

Now that we have a basic understanding of the syntax, let’s explore how we can leverage the INDIRECT function in Google Sheets.

How to Use the INDIRECT Function in Google Sheets

The INDIRECT function can be used in conjunction with other functions to convert their output into an actual reference and incorporate the values stored in the referenced cell.

Using A1 Notation and R1C1 Notation in Google Sheets

Google Sheets offers two reference notation systems: “A1 notation” and “R1C1 notation”.

The “A1 notation” is the default notation, where columns are labeled with letters (starting from A) and rows are labeled with numbers (starting from 1). The first cell of the sheet is marked as A1.

On the other hand, the “R1C1 notation” uses numbers for rows (marked as R) and columns (marked as C). The first cell of the sheet is marked as R1C1.

While we are more accustomed to using the A1 notation, as it is visually represented in the Google Sheets interface, the R1C1 notation has its own advantages, especially when creating dashboards.

Examples of the INDIRECT Function in Google Sheets

If you’re someone who learns best through examples, you’re in for a treat! Here’s a treasure trove of examples that will help you understand how to use the INDIRECT function effectively.

Example 1: Reference Stored Inside the Formula

You can place the reference directly inside the INDIRECT function, enclosed in double quotation marks:

=INDIRECT("reference")

Where reference is the reference of the cell you want to call.

Example 2: Reference and A1 Notation Set to TRUE

Adding the value TRUE for the A1 notation option is optional but recommended to ensure that your formula works seamlessly:

=INDIRECT("reference", TRUE)

Where reference is the reference of the cell you want to call.

Example 3: Using R1C1 Notation

If you prefer using the R1C1 notation in your formula, make sure to set A1_notation to FALSE:

=INDIRECT("reference", FALSE)

Where reference is the reference of the cell you want to call, this time in R1C1 notation. In the following examples, you’ll see the advantages of using the R1C1 notation over the more common A1 notation.

Example 4: Reference Stored in Another Cell

If you input a reference into the formula without enclosing it in double quotation marks, Google Sheets will read the value stored in that cell and interpret it as a reference:

=INDIRECT(reference_storage)

Where reference_storage is the reference of the cell where the reference you want to process using the INDIRECT function is stored.

Example 5: Reference Stored in Another Cell with Absolute Reference

Similar to Example 4 above, but this time the reference has dollar signs, also known as absolute reference. Dollar signs are added to references in Google Sheets to keep the reference fixed even when the formula using it is copied to other adjacent cells.

=INDIRECT(reference_storage)

Where reference_storage is the reference of the cell where the reference you want to process using the INDIRECT function is stored.

Example 6: Reference Stored in Another Cell in R1C1 Notation

Similar to 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 of the cell where the reference you want to process using the INDIRECT function is stored.

Example 7: Column Letter and Row Number Stored in Separate Cells

If the column letter and row number are stored in separate cells, you can combine them and input them into 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.

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.

Example 8: Column and Row Numbers Stored in Separate Cells

If the column number (instead of the column letter) and row number are stored in separate cells, you can combine them into R1C1 notation and input them into the INDIRECT function. There are two ways to achieve this. The formula for the first method is as follows:

=INDIRECT("R" & row & "C" & column)

Where:

• column is where the column number is stored.
• row is where the row number is stored.

The ampersand & combines the strings together.

For the second method, you can also use CONCATENATE:

=INDIRECT(CONCATENATE("R", row, "C", column))

Where:

• column is where the column number is stored.
• row is where the row number is stored.

Example 9: Reference Points to a Cell in Another Sheet

The INDIRECT function can also handle references that point to another sheet. Simply add the sheet name just as you would when referencing ranges from a different sheet, using the same formulas as in the previous examples.

Example 10: Reference Itself Stored in Another Sheet

The INDIRECT function can process the reference that is stored in a cell of another sheet, whether it is stored in its entirety or in parts. Note that to ensure the formula works correctly, either store the reference in its entirety or consolidate it using CONCATENATE within the INDIRECT formula:

=INDIRECT(CONCATENATE(address_to_merge))

Where address_to_merge is the string to be merged into an address pointing to a cell.

Example 11: Using Named Ranges as References

You can use named ranges as references in your formula:

=INDIRECT(named_range)

Where named_range is the named range you want to input, either as a string within double quotation marks or as the reference to where the named range is stored.

Since named ranges are often used to refer to entire ranges of data, the output will be a range containing the data.

Additionally, since named ranges are universal in the spreadsheet, you can use them to refer to cells in other sheets without the complexity of managing references to other sheets.

Example 12: Using a Range as a Reference

You can use ranges as references in your formula:

=INDIRECT(range)

Where range is the range you want to input, either as a string within double quotation marks or as the reference to where the range is stored.

You can also combine separately stored references into a range as input:

=INDIRECT(first_cell & ":" & last_cell)

Where:

• first_cell is the first cell of the range.
• last_cell is the last cell of the range.

Or combine column letters and row numbers stored separately into this formula:

=INDIRECT(CONCATENATE(first_column, first_row, ":", last_column, last_row))

Where:

• first_column and last_column are column letters.
• first_row and last_row are row numbers.

The highlighted formula syntaxes for the formulas above will also work with R1C1 notation, but with some adjustments, including setting A1_notation to FALSE.

Example 13: Combining with MATCH

You can combine the INDIRECT function with the MATCH function to create a search formula that provides you with a range of results. For example, you might want a formula that gives you the range of results for a given set of boundaries. This set of boundaries is typically in the form of dates.

In our example above, we want to find the winning numbers from October 2nd to October 5th. We use the MATCH function to find the position of the boundaries in the range, and then use that to set the range reference to be referenced by the INDIRECT 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 the bounds of the range you’re looking for.
• label_range is the range where the labels for values are stored.
• value_column is the column where the values you’re looking for are stored.

Example 14: Combining with ADDRESS and MATCH to Find the Value Given Column and Row Headers

You can combine the INDIRECT function with ADDRESS to create a lookup formula where the inputs are the column and row headers:

=INDIRECT(ADDRESS(MATCH(column_header, column_header_range), MATCH(row_header, row_header_range)))

Where:

• column_header is the column header you’re looking for.
• column_header_range is where the column headers are stored.
• row_header is the row header you’re looking for.
• row_header_range is where the row headers are stored.

This is a handy formula you can use when looking up specific entries as it utilizes column and row headers, which is the natural way for us to search for values in tables.

Common Issues with the INDIRECT Function

As with any formula, you might encounter some issues when using the INDIRECT function. Here are a few common problems and how to resolve them.

Error #REF Due to an Incorrect Notation

While the default notation used is A1 notation, you might find R1C1 notation helpful in some cases. If you’re not careful, you might receive the #REF error because the formula is not set to use R1C1 notation.

To fix this error, simply add FALSE to the formula.

Error #REF Due to an Invalid String

If the string inputted in the formula is not a valid reference in either A1 or R1C1 notation, the formula will also return the #REF error. To resolve this issue, modify the function or the value in the cell containing the reference to include the actual reference.

Empty Output Due to a Typo in the Reference

If you accidentally misspell the reference in the formula, you might receive an empty output. This occurs because the misspelled reference can point to a cell that actually exists in the sheet but contains no value, resulting in an empty result. To fix this issue, modify the function or the value in the cell containing the reference to include the correct reference.

Error #REF Due to Overlapping

If you’re using named ranges, it’s possible that the size of the referenced range overlaps with adjacent cells that already contain values. This will result in a #REF error.

If you intend to refer to the named range, you’ll need to adjust your sheet or the size of the named range, as well as the placement of the formulas in your sheet.

We hope this article has helped you gain a better understanding of how to use the INDIRECT function in Google Sheets. You might also be interested in our articles on how to freeze a cell in Google Sheets and how to use IMPORTHTML in Google Sheets.

By the way, we highly recommend reading our guide on setting up automated email notifications from Google Sheets and trying out our invoice due date tracking.

For more tips and techniques to maximize your use of Google Sheets, head over to Crawlan.com.

See you soon, besties!