ADDRESS Function Examples

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

Video: ADDRESS Function

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.

Uses for ADDRESS

The ADDRESS function can return a cell address, or combine with other functions to:

• 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 Syntax

The ADDRESS function has the following syntax:

• 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

ADDRESS Traps

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.

Ex 1: Cell address from row and column

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

Absolute or Relative - abs_num argument

There are 4 options for the abs_num argument:

1. Absolute - $C$4
2. Mixed - Absolute row / Relative column - C$4
3. Mixed - Relative row / Absolute column - $C4
4. 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

Reference Style - a1 argument

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"

Sheet Name - sheet_text

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)

Ex 2: Cell value from row and column number

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))

Ex 3: Address of cell with highest value

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))

Ex 4: Column Letter from Column Number

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","")

How It Works

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.

Formula Variation

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","")

Download the Sample File

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

More Tutorials

INDIRECT Function

INDEX and MATCH Functions

CELL

TEXT