Excel ADDRESS function examples show how to get a cell address as text, based on a row and column number.

The ADDRESS function returns a cell address as text, based on a row and column number. It can return an absolute or relative address, in A1 or R1C1 style. A sheet name can also be included in the result.

To see how the ADDRESS function works, watch this short video tutorial. The video uses the sample workbook from this tutorial.

- Get cell address from row and column number
- Find cell value from row and column number
- Return address of cell with highest value
- Get column letter, based on column number

**ADDRESS(****row****_num**,**column_num**,[abs_num],[a1],[sheet_text])**abs_num**-- if 1 or omitted, Absolute ($A$1), for Relative (A1), use 4. Or choose a mixed reference option, 2=A$1, 3=$A1**a1**-- if TRUE or omitted, returns A1 style, FALSE for R1C1 style**sheet_text**-- sheet name can be included, for address on a different sheet

The ADDRESS function only returns the cell address **as text**.

If you need the cell value, use the INDIRECT function around the ADDRESS function, or use one of the other formulas shown in Example 2, below.

With the ADDRESS function, you can get a cell address, as text, based on a row number and column number.

If you enter just the row and column arguments, the result is an **absolute** address, in **A1** style. In the screen shot below, this formula is entered in cell C4:

**=ADDRESS($C$2,$C$3)**

The result is $C$1

There are 4 options for the abs_num argument:

- Absolute - $C$4
- Mixed - Absolute row / Relative column - C$4
- Mixed - Relative row / Absolute column - $C4
- Relative - C4

If you use 1, or omit the abs_num argument, the result is an absolute reference.

In the following formula, there is a 4 entered in the **abs_num** argument:

**=ADDRESS($C$2,$C$3,4)**

Now, the result is shown as a relative reference -- C1

The 4th argument for the ADDRESS function, **a1**, controls the reference style - A1 or R1C.

- If you use TRUE, or omit the a1 argument, the result is shown in A1 style, such as "B15".
- If you use FALSE, the result is shown in R1C1 style, such as "R15C2"

In the following formula, FALSE is the 4th argument:

**=ADDRESS($C$2,$C$3,1,FALSE)**

The result, in cell C4, is an absolute reference (1), shown in R1C1 style -- "R1C3"

The 5th argument for the ADDRESS function is sheet_text. You can type a sheet name in the formula, or refer to a cell that contains a sheet name.

In this formula, the sheet name, "Ex02", is hard-coded into the formula

**=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")**

In the next formula, a sheet name is selected from a drop down list in cell C4, and the formula refers to that cell.

**=ADDRESS($C$2,$C$3,1,TRUE,C4)**

In this example, the goal is to get the value from a specifi cell, based on its row and column number. We'll need two functions to achieve this goal.

- The ADDRESS function returns the cell address as text, not as an actual reference.
- To overcome that limitation, you can combine the ADDRESS function with the INDIRECT function. INDIRECT can return a reference, based on text.

Here is the formula in cell C4, which returns the value from the specified address.

**=INDIRECT(ADDRESS(C2,C3))**

In this example, we'll find the cell with the highest value, and use the ADDRESS function to get its address.

First, in cell F3, the MAX function finds the highest number in column C.

**=MAX(C3:C8)**

Next, in cell F4, the ADDRESS function is combined with two other functions:

- MATCH, which finds the row number
- COLUMN, which gets the column number

Here is the formula from cell F4, with all three functions:

**=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))**

In this example, we'll find a column letter, based on a column number, such as "D" for column 4. Thanks to Luke Wisbey for this formula.

- In cell A3, you can type an valid column number, such as 100.
- In cell B3, the following formula calculates the column letter, based on that number.
**=SUBSTITUTE(ADDRESS(1,A3,4),"1","")**

Here's how the ADDRESS section of the formula works:

- For the row number, 1 is entered
- For the column number, there is a reference to cell A3, which contains 100
- For the abs_num argument, 4 is entered, so the result will be a relative reference

The result of the ADDRESS section, in this example, is **CV1**

Next, the SUBSTITUTE function replaces the "1" in the address with an empty string.

The result of the formula is CV -- the column letter(s), without the row number.

For a variation on this formula, you could use the COLUMN function, without a cell reference, instead of referring to a cell with a column number.

This formula will give the column letter for whatever column the formula is in.

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

To see the formulas used in these examples, download the
**ADDRESS
function sample workbook**. The file is zipped, and is
in Excel xlsx file format. There are no macros in the workbook

Last updated: November 9, 2018 11:08 AM

Contextures RSS Feed