It can sometimes be useful to know the address of a cell in a worksheet, so you can use that address in a formula. In this lesson, we'll look at how to use the ADDRESS() function to find out the address of a cell. We'll use the ADDRESS function in several examples to demonstrate how useful it can be.

ADDRESS() Function Syntax

The ADDRESS() function is used to find the address of a given cell. It has the following syntax:

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

The paramaters for the ADDRESS() function are as follows:

• row_num - the number of the row of the cell you want to find the address for. Required.
• col_num - the number of the row of the cell you want to find the address for. Required.
• abs_num - indicates whether you want to return the cell address as an absolute or relative reference. Optional but recommended. Must be between 1 and 4.
• a1 - indicates whether you want to return the cell address in A1 format or R1C1 format. Optional. The default is A1 which is usually preferred. Must be either TRUE (A1) or FALSE (R1C1).
• sheet_text - allows you to specify the name of another worksheet in which the ADDRESS function should look. Optional.

Let's look at some examples where we can learn how to use these different parameters.

Find the address of a cell

In this simple example, we will supply the row_num and col_num values for the ADDRESS() function:

=ADDRESS(1,4)

In this example, we've asked for the address of the cell in row 1, and column 4 (cell D4). The result of this formula is shown below:

Note that the result has actually been returned as an absolute reference, $D$1. This is because we did not supply the abs_num value for the ADDRESS function, so Excel has used the default value instead. If you want to change how the cell address is calculated, you will need to supply the abs_num value as well, as shown in this example:

As you can see, the different values of the abs_num parameter determine whether to return an absolute, mixed, or relative reference to the cell we specified. If this is a new concept for you, check out our lesson on absolute and relative references (opens in a new window).

Find the address of a cell using R1C1 format

Most people using Excel are familiar with referring to cells using the column letter and rown number, such as A1, B27, AA245. However, Excel offers another way to refer to cells, called R1C1 format, where you refer to cells by their Row number and Column number, such as R1C1, R27,C2, and R245C27. In my experience, it is rare to use R1C1 referencing, but it can be very useful in certain circumstances. Either way, the ADDRESS function allows you to specify which format to return the result in, as shown here:

If we combine this formula with the abs_num parameter we saw earlier, we get this result:

Note the difference between the first and second examples shown here. The first example excludes the abs_num parameter, so Excel uses the default value of 1. The other examples specify different options for abs_num, which return different versions of the cell reference using R1C1 formatting. You can compare these to the examples shown earlier using A1 referencing.

Find the address of a cell in a different workbook

The last parameter in the ADDRESS function allows you to specify which sheet, as well as which cell. Here are a couple of examples:

First, here's how to return the address of a cell in another worksheet called January in the current workbook:

Second, here's how to return the address of a cell in a worksheet called January, in another workbook called Sales.xlsx:

Find the address of the current cell

Now that we understand how to use the ADDRESS function, let's extend it to find the address of the current cell, i.e. the cell in which we are writing our formula.

To do this, we'll need to figure out which row and which column the current cell is in. Excel provides the ROW() and COLUMN() functions to do just this.

In this example, we'll use the ROW and COLUMN functions with no parameters to return the ROW number and COLUMN number of the current cell. We'll also set the abs_num parameter to 4 so the result is a relative reference, rather than defaulting to an absolute reference:

=ADDRESS(ROW(),COLUMN(),4)

Use the ADDRESS and INDIRECT functions to find the value in another cell

So far, we've looked at how to find out the address of a cell in our worksheet. Now we can look at how to use it to find out the value in that cell.

To do this, we need a way to write a formula that tells Excel to retrieve the value in a cell, given the address of the cell. The INDIRECT function is designed to do just that.

A simple example of the INDIRECT function is this formula which will return the value in cell E26. Note that as far as the INDIRECT function is concerned, "E26" is just text. The job of INDIRECT is to convert into a cell reference:

=INDIRECT("B26")

We can replace "B26" in this example with the ADDRESS function, e.g.

=INDIRECT(ADDRESS(26,2,4))

We can also use the ROW and COLUMN functions:

=INDIRECT(ADDRESS(ROW(B28),COLUMN(B28)))

Here's what these examples look like:

Of course, Formula2 and Formula3 as shown in the picture above are somewhat pointless, since we've hard coded the cell reference to B26 into the formule, which means we could replace both formulas with a simpler formula, =B26, instead. But what this formula opens up is the possibility of using other methods to calculate which row and which column to use within the ADDRESS function.

Summary

In this lesson, we looked at the ADDRESS function and how to use it to return the address of a cell based on row and column data that we supplied. We looked at different ways to calculate the ROW and COLUMN values to use with the ADDRESS function. We also learnt how to use the INDIRECT function to find out the value in the cell address returned by the ADDRESS function.

If you would like to learn more about how to use the ADDRESS function, check out this lesson on how to find the name of the highest selling sales person in the month.