Discover the ADDRESS function in Google Sheets [Easy Guide]

Do you want to unlock the hidden power of Google Sheets? Look no further! We’re here to introduce you to the ADDRESS function in Google Sheets, and show you how it can be incredibly useful. You might not have heard of it before, but once you understand its potential, you’ll wonder how you ever lived without it.

What does the ADDRESS function do?

The ADDRESS function takes the row and column index of a cell and returns a reference to that cell as a text string. For example, if you provide the function with a row number of 1 and a column number of 2, it will return a reference to the cell $A2.

At first glance, the ADDRESS function may seem underwhelming. But don’t be fooled! Its real power lies in its ability to work in tandem with other functions and formulas.

Let’s dive into the basics of the ADDRESS function.

Syntax of the ADDRESS function

If you’re familiar with using the ADDRESS function in Excel, you’ll find that the syntax of the ADDRESS function in Google Sheets is quite similar.

The syntax of the ADDRESS function is as follows:

ADDRESS(row, column, [absolute_relative_mode], [use_A1_notation], [sheet])

Here’s what each parameter means:

• row is the row number of the cell for which you want a reference string.
• column is the column number of the cell for which you want a reference string.
• absolute_relative_mode is an optional integer that specifies whether the returned reference should be an absolute or relative reference. It has a default value of 1.
• use_A1_notation is an optional TRUE/FALSE value that determines whether the returned reference should be in A1 notation or R1C1 notation. It has a default value of TRUE.
• sheet is an optional string that specifies the name of the sheet to which the address should point.

The absolute_relative_mode parameter can take one of four values:

1. Setting it to 1 indicates that the returned reference should be both an absolute row and column reference (like $A$1).
2. Setting it to 2 indicates that the row should be a relative reference and the column should be an absolute reference (like A$1).
3. Setting it to 3 indicates that the row should be an absolute reference and the column should be a relative reference (like $A1).
4. Setting it to 4 indicates that both the row and column should be relative references (like A1).

Additionally, when the use_A1_notation parameter is set to TRUE, the ADDRESS function returns the cell reference in A1 notation. So, a reference to a cell in row 2 and column 3 would be returned as C2.

When set to FALSE, it returns the cell reference in R1C1 notation. This means that a reference to a cell in row 2 and column 3 would be returned as R2C3.

Please note: In R1C1 notation, a relative row or column number is surrounded by square brackets, while an absolute row or column is indicated by the absence of brackets. This means that a cell with an absolute row number of 2 and a relative column number of 3 would be returned as R2C[3].

If the sheet parameter is omitted, it is assumed that we are looking for a reference to a cell in the same sheet.

How to use the ADDRESS function in Google Sheets

It can be a little tricky to understand how the ADDRESS function works without seeing it in action. So, let’s take a look at a few examples to see how the ADDRESS function is used and how its results change with different parameters.

The image below showcases some examples of the ADDRESS function and the cell reference strings it returns:

From the examples above, you can see that the ADDRESS function simply returns the reference to cell D2. It doesn’t return the content of the cell itself.

Applications of the ADDRESS function

While using the ADDRESS function on its own may not seem very useful, when combined with other functions, it becomes a powerful tool. Let’s explore a few examples to see how it can be leveraged.

Using the ADDRESS and INDIRECT functions to retrieve the content of a cell based on a given row and column

The INDIRECT function returns the value of a cell, given a text string containing its address. So, the following formula retrieves the value in cell “D2”:

=INDIRECT("D2")