Most people use cell references when writing formulas on Google Sheets. For example, suppose you’re adding cells A1 and A2—you’ll likely use the formula A1+A2 to find the solution. But what if you don’t know the cell references you need to work with? This is where the ADDRESS function comes in.
The ADDRESS function in Google Sheets will always return a reference in text string format to a single cell. You can employ the ADDRESS function to construct a cell reference inside a formula, which will either return an absolute or relative reference.
This is an advanced function, but if you interact with Google spreadsheets sufficiently, you will discover it has some critical applications. Read on to learn everything you need about the basics of Google Sheets’ ADDRESS function.
When to use the ADDRESS Function
The ADDRESS function is a built-in function found in Google Sheets. It belongs to the group of functions known as Lookup functions.
In short, it turns column and row numbers into cell addresses.
This function aims to construct a cell reference from a column and row number. For instance, the ADDRESS function will return a reference to the cell $A2 if we provide it with the row and column numbers 1 and 2, respectively.
The address function does not seem to do much from the definition, which is why it is not frequently used. One might think that learning Google Sheets’ ADDRESS function is a waste of time. By the end of this guide, however, you will understand the value of this function, mainly when used in conjunction with other functions or formulas.
ADDRESS Function Syntax
The following is the syntax for the ADDRESS function:
=ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])
As you’ve seen above, It’s quite an extended syntax. However, many of these arguments are optional and not often used. Now, we will take a closer look at each phrase to gain a better understanding of what they do:
- = (equals sign): In Google Sheets, the equals sign tells the program that the text inside is a formula.
- ADDRESS(): is our Google Sheet’s function. It lets Sheets know which calculation to perform.
- row: indicates the row number corresponding to the cell. It’s a required argument.
- Column: In the same way as the row argument, it indicates the number of the column in which the cell is situated for which we want the address. It is also a required argument.
- absolute_relative_mode: It is an optional argument that acknowledges one of four possible values, 1, 2, 3, or 4, each of which corresponds to one of the four possible modes of cell references. They comprise:
- 1: If the returned reference has a value of 1, we want both the row and column to be absolute references (i.e., $A$1).
- 2: If the number is 2, we want the row references to be relative and the column references to be absolute (i.e., A$1).
- 3: If the number is 3, we want the row references to be absolute and the column references to be relative (i.e., $A1).
- 4: If the number is 4, then we want to use both row and column as relative references (i.e., A1)
- use_a1_notation: This optional argument specifies if to use R1C1 style notation (FALSE) or A1 style notation (TRUE). Its default value is true.
- sheet: It’s a string containing the sheet’s name that the address refers to. It’s an optional parameter. If the sheet parameter is missing, it is presumed that we are seeking a reference to a cell located in the same worksheet.
If you need to know more about which parameter to select for the absolute_relative_mode argument, check out this complete guide on absolute references for Excel. They work the same in Google Sheets.
Examples of Google Sheets ADDRESS Function
Seeing ADDRESS put to use is the most effective approach to understanding it, Several possible combinations of the said function are shown below. Study them and note the differences.
The earlier examples showed that the ADDRESS function only returns a reference to cell D2 when called. It does not retrieve the data stored in the cell. It’s not much use using the ADDRESS function on its own. However, when paired with other features, it may be pretty valuable. Let’s examine some instances to learn how.
INDIRECT and ADDRESS Functions.
The content of a specific cell reference held in another cell is shown using the INDIRECT function. It is perfect for removing circular references in your spreadsheet. The function’s most basic form is as follows:
=INDIRECT(cell)
Here’s how to use INDIRECT:
- Into your desired cell, input the syntax =INDIRECT(“A4”) and substitute A4 for the cell you’re trying to fetch.
- Hit Enter. In our example, Huawei is returned as the string from cell A4.
Now let’s say we didn’t know the cell reference but had the row and column data stored elsewhere. If we combine the INDIRECT and ADDRESS methods, we can easily retrieve the contents of any required cell.
For this example, we know that “Huawei” falls on row 4 and column 1, so we’ve placed the row and column numbers into the spreadsheet.
The ADDRESS function alone won’t be sufficient to determine the value in the requested cell—only the position of cell $A$4 is provided, as shown below.
But if we enclose it in an INDIRECT function, we can read the contents. The whole formula would look like this:
=INDIRECT(ADDRESS(D2,E2))
Here are the results we’d get from that formula:
Although it’s easy to look up values in such a small data set, this formula is excellent at finding values when you have massive spreadsheets with many rows and columns.
As you can imagine, you can nest the ADDRESS in plenty of other search functions, but we’ll save those for a more advanced lesson.
Winding Up the Google Sheets Address Function Guide
The ADDRESS Google Sheets function is seldom used for a reason. It is much more complicated than other search functions, such as XLOOKUP and VLOOKUP. But you may still have to use it in some niche situations, so it’s still handy to have a basic understanding of it.
You’d mainly have to interact with the ADDRESS function when using other people’s spreadsheets. If you’re building your own, we recommend using a LOOKUP function instead.
Read the full article here