The Basics of the Google Sheets ADDRESS Function
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.
Sign up forfree
Forgot your password?
Create an account
*Required: 8 chars, 1 capital letter, 1 number
By continuing, you agree to thePrivacy PolicyandTerms of Use.You also agree to receive our newsletters, you may opt-out any time.
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:
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:
If you need to know more about which parameter to select for theabsolute_relative_modeargument, check out thiscomplete guide on absolute referencesfor 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 forremoving circular referencesin your spreadsheet. The function’s most basic form is as follows:
Here’s how to use INDIRECT:
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:
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 ofother 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.
Using VLOOKUP, you can retrieve values from a specific row in a table by performing a vertical lookup. Here’s how to use it in Google Sheets.
So much time invested, and for what?
Windows is great, but adding this makes it unstoppable.
When your rival has to bail out your assistant.
Freeing up vital memory on Windows only takes a moment, and your computer will feel much faster once you’re done.
It’s not super flashy, but it can help to keep your computer up and running.
