Excel ADDRESS Function – How To Use

The ADDRESS function in Excel enables users to create a cell reference by providing the row and column numbers of a cell. The function is particularly useful when working with large datasets or creating dynamic formulas that need to reference different cells based on changing criteria.

The ADDRESS function returns a text string that identifies the cell's location in a worksheet in relative, mixed, or absolute format. The return result can be either an A1 type or R1C1 style.

Excel ADDRESS Function

Syntax

The syntax of the ADDRESS function is as follows.

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

The ADDRESS function accepts five arguments out of which two are mandatory and the remaining are optional. Details of each argument are mentioned below for a thorough understanding.

Arguments:

'row_num' -This is a required argument that specifies the row number of the cell of which we want to create a reference. It can be a direct value or a cell reference containing the value. The row number must be a positive integer.

'column_num' – This is also a required argument that specifies the column number of the cell of which we want to create a reference. The column number must be a positive integer and it can either be a direct value or a cell reference containing the value.

'abs_num' – This is an optional argument that specifies the type of reference that we want to create. Depending on the input value from 1 – 4, the return cell reference can be a relative, absolute, or combination reference.

The default value of the abs_num argument is 1 which refers to the absolute cell reference ($A$1). If abs_num is set to 2, the returned reference is a mixed reference with an absolute row and relative column (A$1). If the value of the abs_num is 3, the resulting cell reference is again a mixed reference with relative row and absolute column ($A1). Lastly, if the abs_num value is 4, the return cell reference is completely relative (A1).

'a1' – This is another optional argument that specifies the reference style we want to use. It is a boolean argument where the default value is 1 or TRUE which indicates an A1 style reference (e.g., A1, B2, C3). If the value of the a1 argument is 0 or FALSE, the reference is returned in R1C1 style (e.g., R1C1, R2C3, R3C4) where R means row and C means column.

'sheet_text – This last argument is also an optional argument that contains the worksheet name to be used as a reference. The default value of the argument is the current worksheet. If we wish to refer to an external worksheet, the input value of the sheet_text argument must be the sheet name enclosed in double quotes.

Important Characteristics of the ADDRESS Function

Some of the noteworthy features of the ADDRESS function are as follows.

  • The resulting cell reference of the ADDRESS function is always a text string.
  • If the value of the row_num or column_num argument is zero or negative, the ADDRESS function throws a #VALUE! error.
  • When the value of the abs_num argument is anything other than 1-4, the function returns a #VALUE! error.

Examples of ADDRESS Function

ADDRESS function is a powerful tool used when dealing with several worksheets and large datasets. Using the examples mentioned below, let's discover more applications of the said function.

Example 1 – Simple Use of ADDRESS Function

In this example, we will explore the various arguments of the ADDRESS function and its usage by providing examples with different input values. This will allow us to gain a comprehensive understanding of how to utilize each argument and achieve the intended result.

Simple Use of ADDRESS Function

Each column in the example displays distinct input values for each argument, providing a clear visual representation of the various options available for customization of the ADDRESS function. To showcase the role of the optional arguments, we have set the row and column numbers to 2 (as shown in columns B and C) in all the examples. Row 2 and column 2 refer to cell B2. Let's see what types of cell references we can get for B2 with and without ADDRESS function's different optional arguments.

In the first example, the ADDRESS function is used with only the mandatory arguments. The row and column numbers are set to 2, and the remaining arguments will assume their default values. The resulting output is a simple absolute reference in A1 style, which denotes the second column 'B' with the second row 2, resulting in the output "$B$2".

In the next two examples, the ADDRESS function is used with the abs_num argument set to 2 and 3, respectively. As a result, a mixed cell reference is generated i.e. B2. In the fourth example, the abs_num argument is set to 4, resulting in a relative reference to the second row and second column, which is "B2". These examples showcase the versatility of the abs_num argument in customizing the type of cell reference generated by the ADDRESS function.

The next two examples will help us understand the usage of the a1 argument. The row and column numbers are set to 2. In both cases, the value of the a1 argument is 0. When the abs_num is set to 1, the function returns an absolute cell reference in R1C1 style whereas when it is set to 4, the function results in a relative cell reference in R1C1 style which is "R[2]C[2]".

The last example generates an absolute reference in A1 style that points to cell B2, with the added text string "ADDRESS Function" as the sheet name.

Now that we have covered the basic functionality of the ADDRESS function, let's explore more examples where we combine it with other functions such as INDIRECT and MATCH. This will allow us to further enhance the flexibility and usefulness of the ADDRESS function in different contexts.

Example 2 – Finding Address of First Cell in Range

When dealing with large data, it is often a neat trick to determine the address of the first and the last cell of the range. The ADDRESS function can be used to get the cell reference pointing to the first and the last cell. To better understand how it works, we have a sample data set containing the country names, their capital, and their currency.

Finding Address of First Cell in Range

To get the address of the intended cells, we require input values of the first two arguments of the ADDRESS function which are row_num and column_num. We can use a combination of ROW, COLUMN, and MIN functions to get the values of the said arguments.

The ROW and COLUMN functions will return the row and column numbers from the given range. When wrapped in the MIN function, we will get the smallest row and column number. Let's put the concept in Excel form. The formula used will be as follows.

=ROW(B4:D18)
Finding Address of First Cell in Range

Now that we have all the row numbers from the cell range, using the MIN function, we can fetch the smallest row number. The formula will be as follows.

=MIN(ROW(B4:D18))
Finding Address of First Cell in Range

Now that we have the first or smallest row number, we can use this as an input value for the first argument row_num. Using the same logic, we can find the first column number using the following formula.

=COLUMN(B4:D18)
Finding Address of First Cell in Range

Adding the MIN function to the above formula, we will have the smallest or the first column number in the cell range.

=MIN(COLUMN(B4:D18))
Finding Address of First Cell in Range

Now that we have both the input values, let's compile the final formula.

=ADDRESS(MIN(ROW(B4:D18)),MIN(COLUMN(B4:D18)))
Finding Address of First Cell in Range

That's how you get the address of the first cell from a cell range using the ADDRESS function.

Example 2.1 – Finding Address of Last Cell in Range

Using the same logic, we can also determine the address of the last cell from a cell range. The only difference would be that then we were trying to locate the smallest or the first row or column number for the first cell address, whereas, for the last cell address, we will locate the maximum or the last row and column number.

In Excel form, the function will look like this.

=ADDRESS(MAX(ROW(B4:D18)),MAX(COLUMN(B4:D18)))
Finding Address of Last Cell in Range

Here, we have the reference of the last cell in A1 form. If we wish to get the desired cell reference in R1C1 style, we can set the value of the a1 argument to 0. The updated formula will be as follows.

=ADDRESS(MAX(ROW(B4:D18)),MAX(COLUMN(B4:D18)),1,0)
Finding Address of Last Cell in Range

When dealing with large datasets, the R1C1 referencing is easier to understand. Try to find the first and last cell addresses on a bigger database while practicing.

Example 3 – Getting Cell Value with Given Row and Column

In this example, we have a dataset of random color names. By entering the row and column number, we want to extract the value from that cell. This can be helpful when we randomly wish to extract a value from a larger dataset.

Getting Cell Value with Given Row and Column

The user can enter any row number and column number in cells G1 and G2, respectively. Using the ADDRESS function, combined with the INDIRECT function, we can pull out the value from the intended cell. The INDIRECT function will take the resulting text string from the ADDRESS function and return the value of the corresponding cell.

The formula used will be as follows.

=INDIRECT(ADDRESS(G1,G2))
Getting Cell Value with Given Row and Column

This is an excellent way of also creating dynamic cell references and extracting data from large datasets. A similar result can also be achieved using INDEX and MATCH functions.

Now, what if instead of row and column numbers, the user wishes to determine the position of the largest value in a dataset? Let's find out in the next example.

Example 4 – Extracting Cell Address with Highest Value

In this scenario, we have the data containing monthly sales made by each salesman. Usually, such datasets are large, and it is difficult to find the exact cell reference to the desired data. Here, we wish to find the highest sales done each month and pull out its exact cell reference.

Extracting Cell Address with Highest Value

To find the highest sales value in a month, we need to find the largest value in columns C. The formula used will be as follows.

=MAX(C3:C12)
Extracting Cell Address with Highest Value

Now that we have the highest sales value for January month. We can use the MATCH function to find the position of the highest value in that column. The formula used will be as follows.

=MATCH(MAX(C3:C12),C:C,0)
Extracting Cell Address with Highest Value

We now know that in January month, the highest sales value lies on row 11. We can now use the ADDRESS function to extract the exact cell reference using the return value of the MATCH function as the input value for row_num and stating column_num as per the month which will be 3 for January. The formula used will be as follows.

=ADDRESS(MATCH(MAX(C3:C12),C:C,0),3)
Extracting Cell Address with Highest Value

We now have the desired result. We can go a step ahead and extract the name of the salesperson who made the highest sales each month.

To get that information, instead of using the column_num for the month, we can use 2 as the input value for column_num and then wrap it in the INDIRECT function as we did in the above example. The formula used will be as follows.

=INDIRECT(ADDRESS(MATCH(MAX(C3:C12),C:C,0),2))
Extracting Cell Address with Highest Value

Using the same logic, we can also extract the cell address with the lowest value in a dataset.

Explore more useful ways to use the ADDRESS function in Excel, while we curate yet another intriguing Excel function for you.

About Shubhra Jain

Meet Shubhra Anand Jain, a dedicated Excel enthusiast with over 5 years of expertise, specializing in data analysis, complex formula development, and automation. Based in Sweden, she's on a mission to simplify Excel, one formula at a time. Check out Shubhra's extended profile here.