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 ADDRESS function Google Sheets, show you what it does and how you can use it. We will also show you some applications in which the Google Sheets ADDRESS function can be quite helpful.
Table of Contents
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.
The 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 the ADDRESS 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])
Here,
- 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 the row as relative and the column as absolute references (as in A$1)
- A value of 3 indicates that we want the row as absolute and the 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[3].
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
Like the OFFSET 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 in Google Sheets 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”:
=INDIRECT(“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 that 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 numbers 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, Google Sheets return cell references which 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:
=INDIRECT(ADDRESS(B1,B2))
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 SUBSTITUTE Functions to Translate Column Numbers to Letters
When combined with the SUBSTITUTE function, the Google Sheet ADDRESS function can translate column numbers into letters. This can be used if you have a list of column numbers and want to convert them into their respective column letters.
For example, in our sheet below, we have the column numbers.
To convert them to column letters, we can use the formula:
=SUBSTITUTE(ADDRESS(1,B2,4),"1","")
The result will be the column letter for the column number in column B. You can drag the little box at the bottom right of the cell to copy the formula to the rest of the cells.
Although this is a strange use of the ADDRESS function, you never know when it may come in handy.
Using ADDRESS and MATCH Functions to Get the Address of Matching Cell
We can also use the ADDRESS spreadsheet Function to do the opposite, i.e., given a particular value, we can use it to find its location. This can come in 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 will 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 the index in the given range), you will need to combine the MATCH function with the ADDRESS function as follows:
=ADDRESS(MATCH("Red",D:D,0),4)
This method also works great when you want to find partial matches, as shown below:
Using the ADDRESS Function to Locate the Cell with the 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:
=ADDRESS(MATCH(MAX(C,C),$B:$B,0),COLUMN(B2))
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
Another case in which the ADDRESS Google Sheets function comes in quite handy is in locating a named range or getting the complete address that the named range covers.
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:
=ADDRESS(ROW(Colors),COLUMN(Colors))
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 cell reference of the last cell in the range, we can use the formula:
=ADDRESS(ROW(Colors)+ROWS(Colors)-1,COLUMN(Colors)+COLUMNS(Colors)-1)
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:
=ADDRESS(ROW(Colors),COLUMN(Colors))&":"&ADDRESS(ROW(Colors)+ROWS(Colors)-1,COLUMN(Colors)+COLUMNS(Colors)-1)
This formula can help you locate any named range now, as shown below:
Frequently Asked Questions
How Do I Find the Cell Address in a Google Sheet?
The ADDRESS function is used to find the cell address in Google Sheets. You can use it on its own or combine it with other functions to add to its functionality. You can use the ADDRESS function together with the index-match and cell functions.
The index-match formula will help look up the range, while the cell function will return the cell address instead of the value in the cell. For example, in our sheet below, we can get the cell address for Lois Silvia.
This is the formula we will use:
=CELL("address",INDEX(B2:F11,MATCH(I2,B2:B11,0),1))
The 1 at the end of the formula stands for the column id you want to reference from. The result of this formula is $A$9, which is the cell address for the name Lois Silvia.
You can also use the MATCH function with the ADDRESS function to get the cell address, as we have shown you above.
How Do You Identify a Cell Address?
A cell address is usually identified with the column letter and the row number combined. For example, the cell address C7 stands for the cell in column C, row 7. When you select a cell in Google Sheets, the cell address or cell references can be found on top of the sheet below the toolbar at the very left. You can use the address function in Google Sheets to get your cell address.
Conclusion
In this tutorial, we went over the Google Sheets ADDRESS function, its syntax, and the different ways in which you can use it. It’s encouraging to know that the ADDRESS function in Google Sheets as well as in Excel, works more or less the same way. So you can use the techniques discussed in this tutorial in both spreadsheet software. We hope this was helpful. You can also download our Free Google Sheets Address Book Template.
