Not many people are familiar with the ADDRESS function, since many don’t understand why they need it or how it can help.
In this tutorial, we will get you familiar with the Google Sheets ADDRESS function, show you what it does and how you can use it. We will also show you some applications in which the ADDRESS function can be quite helpful.
What Does the Google Sheets ADDRESS Function Do?
The ADDRESS function takes the row and column index of a cell and returns a reference to the cell in the form of a string.
For example, if we give the function a row number of 1 and column number 2, the ADDRESS function returns a reference to the cell $A2.
From the definition, it does not sound like the address function does much, and that is precisely why it is rarely used on its own. You will usually find this function in combination with other functions or formulae.
But before we get to its applications, let us look at the basics of the function.
Syntax for the Google Sheets ADDRESS Functions
If you know how to use the ADDRESS function in Excel, you’ll find that the syntax for this function in Google Sheets is quite similar.
The syntax for the ADDRESS function is as follows:
ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])
- row is the row number of the cell for which we want a reference string.
- column is the column number of the cell for which we want a reference string.
- absolute_relative_mode is an integer that specifies if we want the returned reference to have an absolute or relative row/column. This parameter is optional, with a default value of 1.
- use_a1_notation is a TRUE/FALSE value that specifies if we want the returned reference to be in A1 style notation or R1C1 style notation. This parameter is optional, with 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 be one of 4 values:
- A value of 1 indicates that we want the returned reference to have both row and column as absolute references (as in $A$1)
- A value of 2 indicates that we want row as relative and column as absolute references (as in A$1)
- A value of 3 indicates that we want row as absolute and column as relative references (as in $A1)
- A value of 4 indicates that we want both row and column as relative references (as in A1)
Besides this, when the a1_notation parameter is set to TRUE, the ADDRESS function returns the cell reference in A1 style notation. So the reference to the cell that is in row 2 column 3 is returned as C2.
When it is set to FALSE it returns the cell reference in R1C1 style notation. This means the reference to the cell that is in row 2 column 3 is returned as R2C3.
Note: In the R1C1 style notation, a relative row or column number is surrounded by a pair of square brackets, while an absolute row or column is indicated by the absence of square brackets. This means a cell with absolute row number 2 and relative column number 3 is returned as R2C.
In the absence of the sheet parameter, it is assumed that we are looking for a reference to a cell that is in the same worksheet.
How to Use the Google Sheets ADDRESS Function
It can be a little difficult to understand how the Google Sheets ADDRESS function works without seeing it in action.
Let us look at a few examples to see how the ADDRESS function is used and how its results change with different parameter values.
The following image shows some examples of the ADDRESS function and the cell reference strings that it returns:
From the above examples, notice that the ADDRESS function simply returns the reference to cell D2. It does not return the contents of the cell.
Applications of the ADDRESS Functions
Using the ADDRESS function on its own hardly helps. However, it can be quite helpful when combined with other functions. Let’s see how, by looking at some examples.
Using ADDRESS and INDIRECT Functions to Get Cell Content at Given Row and Column
The INDIRECT function returns the value in a cell, given a string that contains a location. So the following formula returns the value in cell “D2”:
Note that D2 is a string containing the location of a cell and not a cell reference.
The ADDRESS function, on the other hand, returns a string containing the location of a cell. This means, if we can combine the ADDRESS and INDIRECT functions, we get a great way to extract the contents of any cell we need, if we know its row and column numbers.
Consider the layout shown below, where we have a list of colors in column D. In cells B1 and B2, we have the row number and column number for a given cell. If we want to know the value contained in the cell that coincides with the given row and column number, using the ADDRESS function alone will not be enough:
As you can see, all we get is the location of the cell. But if we want to see the contents, we need to wrap an INDIRECT function around it. So we use the formula:
Now we have a formula that can give us the contents of any cell, given the row and column numbers.
This formula can be quite helpful in locating values when you have large datasets that span over many rows and columns.
Using ADDRESS and MATCH Functions to Get Address of Matching Cell
We can also use the ADDRESS Function to do the opposite, i.e. given a particular value, we can use it to find its location. This can come in really handy when you want to look for a particular value in a large dataset.
However, to accomplish this, we will need to combine the ADDRESS function with the MATCH function.
The MATCH function returns the relative position of an item in a given range. This means, if we want to look for the word “Red” in the dataset shown below, the MATCH function would return the index of the cell in relation to the given range.
In this case, since the color “Red” is the second cell in the range D2:D5, we get the result as 2. If you want to know the location of the cell in the entire sheet (instead of just index in the given range), you will need to combine the MATCH function with the ADDRESS function as follows:
This method also works great when you want to find partial matches, as shown below:
Using the ADDRESS Function to Locate the Cell with Highest or Lowest Value
You can also use the combination of MATCH and ADDRESS functions to locate the cells with the highest or lowest values.
For example, say you have the following sales data and want to locate the cell with the highest sales value.
The best way to go about this is to use the MAX function to find the largest value in the range C2:C7.
Next, insert this into the first parameter of the MATCH function in the same formula. So your formula would now be:
Here’s the result we get:
Here, the first parameter of the ADDRESS function is MATCH(MAX(C2:C7),C:C). In other words, it is the row number corresponding to the highest number in the range C:C (or in column C). The second parameter contains the column number that we want to search. In this case, it is column C.
Similarly, we could use the MIN function to locate the cell with the lowest sales value, as shown below:
To Locate and Get the Complete Address of a Named Range
Let’s say we have a worksheet that contains a range of cells under the name ‘Colors’.
To locate this range in the worksheet or to get a reference to this range of cells, we can use the ADDRESS function as follows:
Thus, no matter if your named range is static or dynamic, we can use the above formula to locate the range of cells that this named range points to.
Here, the ROW function returns the row of the first cell in the named range (1 in this case) and the COLUMN function returns the column of the first cell in the range (4 in this case).
The ADDRESS function then returns the address of the cell in row 1, column 4, which is $D$1.
We can also tweak this formula to get the location of the last cell in the range (the one at the bottom right corner). All we need to do is locate the row and column of the first cell in the named range and add to it the number of rows and columns in our range respectively. This gives the location of the cell that is one row below and one column to the right of the cell.
So we subtract one from each to obtain the last cell in the named range.
To find the number of rows in a range, we can use the ROWS function. Similarly, to find the number of columns in a range, we can use the COLUMNS function.
So to get the address of the last cell in the range, we can use the formula:
Now, if we can find both the first and last cells in a named range, we can quite easily find the complete address of the range. All we need to do is concatenate the first cell location string with a semicolon, followed by the last cell location of the string, as follows:
This formula can help you locate any named range now as shown below:
In this tutorial, we went over the Google Sheets ADDRESS function, it’s syntax, and different ways in which you can use it. It’s encouraging to know that the ADDRESS function in Excel as well as in Google Sheets works more or less the same way. So you can use the techniques discussed in this tutorial in both spreadsheet softwares. We hope this was helpful. You can also download our Free Google Sheets Address Book Template.