Most people use cell references when writing formulas in Google Sheets. For example, if you’re adding cells A1 and A2, you might 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 handy.
The ADDRESS function in Google Sheets always returns a text-string reference for a cell. You can use the ADDRESS function to create cell references within a formula, and it will return either an absolute or relative reference.
It’s an advanced function, but if you interact with Google Sheets frequently, you’ll find it has some important applications. Read on to learn everything you need to know about the ADDRESS function in Google Sheets.
When to Use the ADDRESS Function?
The ADDRESS function is a built-in function in Google Sheets. It belongs to the group of Lookup functions. In essence, it converts the column and row numbers into a cell address.
This function is designed to create a cell reference from column and row numbers. For example, the ADDRESS function will return a reference to cell $A2 if we provide it with the corresponding row and column numbers of 1 and 2.
By its definition, you might think the ADDRESS function doesn’t do much, which is why it’s not commonly used. One might think that learning the ADDRESS function in Google Sheets is a waste of time. However, by the end of this guide, you will understand the value of this function, especially when used in conjunction with other functions or formulas.
ADDRESS function syntax
Here is the syntax of the ADDRESS function:
=ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])As you can see above, this is a fairly extensive syntax. However, many of these arguments are optional and are not commonly used. Now, let’s take a closer look at each argument to understand what they do:
= (equals sign): In Google Sheets, the equals sign lets the program know that the text inside is a formula.
ADDRESS(): This is the Google Sheets function that tells the spreadsheet to perform a specific operation.
row: Specifies the row number corresponding to the cell. It is a required argument.
column: Similar to the row argument, it specifies the column number of the desired cell address. It is also a required argument.
absolute_relative_mode: This is an optional argument that accepts one of four possible values: 1, 2, 3, or 4, each corresponding to one of the four possible modes of cell referencing. They include:
1: If the returned reference has a value of 1, both the row and column are absolute references (i.e., $A$1).
2: If it is 2, the row references are relative and the column reference is absolute (i.e., A$1).
3: If it is 3, the row references are absolute and the column reference is relative (i.e., $A1).
4: If it is 4, both the row and column are used as relative references (i.e., A1).
use_a1_notation: This optional argument specifies whether to use R1C1 notation (FALSE) or A1 notation (TRUE). Its default value is true.
sheet: It is a string that contains the name of the worksheet that the address refers to. This is an optional parameter. If the sheet parameter is omitted, it means you are looking for a reference to a cell within the same worksheet.
If you need to know more about which parameter to choose for the absolute_relative_mode argument, refer to this comprehensive guide on absolute referencing for Excel. They work similarly in Google Sheets.
Examples of the ADDRESS function in Google Sheets
Examining how ADDRESS is used is the most effective approach to understanding it. Some possible combinations of the mentioned function are displayed below. Study them and note the differences.
The previous examples demonstrated that the ADDRESS function only returns a reference to cell D2 when called. It does not retrieve the data stored in the cell. It is not used on its own very often. However, when combined with other functions, it can be quite valuable. Let’s consider some scenarios.
The INDIRECT and ADDRESS Functions
The content of a specific cell reference is held in another cell and displayed using the INDIRECT function. It is perfect for eliminating circular references in your spreadsheet. The basic form of the function is as follows:
=INDIRECT(cell)This is how to use INDIRECT:
- In the desired cell, enter the syntax =INDIRECT(“A4”), replacing A4 with the cell you are trying to reference.
- Press Enter. In the example, “Huawei” is returned as a string from cell A4.
Now, let’s suppose you don’t know the cell reference but have stored the row and column data elsewhere. By combining the INDIRECT and ADDRESS functions, you can easily access the content of any desired cell.
In this example, we know that “Huawei” is located on row 4 and column 1, so the row and column numbers are inputted into the spreadsheet.
The ADDRESS function alone is not sufficient to determine the value in the requested cell – only the location of cell $A$4 is provided, as shown below.
But if we wrap it within an INDIRECT function, we can read the content. The complete formula would be as follows:
=INDIRECT(ADDRESS(D2,E2))This is the result obtained from that formula:
While it is straightforward to look up values in a small dataset like this, this formula is particularly excellent for finding values when you have a large spreadsheet with many rows and columns. As you can imagine, you can nest ADDRESS within multiple other lookup functions.
The ADDRESS function in Google Sheets is rarely used for a reason. It is much more complex compared to other lookup functions like XLOOKUP and VLOOKUP. However, you may still need to use it in certain appropriate cases, so it’s worth understanding the basics of this function.
You will primarily interact with the ADDRESS function when working with someone else’s spreadsheet. If you are building your own spreadsheet, the article suggests using the LOOKUP function as a replacement.
