Home > Formulas > Text > Address

ADDRESS Function Examples

Excel ADDRESS function examples show how to get a cell address as text, based on a row and column number. Combine with INDIRECT function, to get a specific cell's value. Or, combine with COLUMN function, to get the column letter for any cell.

address function with sheet list

Video: ADDRESS Function

The ADDRESS function returns a cell address as text, based on a row and column number in the spreadsheet. 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 that you can download at the bottom of this page..

Uses for ADDRESS

The ADDRESS function can return the address of a cell, 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 is one of the Excel Lookup and Reference functions, and it has the following syntax:
  • ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])

Here are the ADDRESS arguments - the first 2 are required arguments in ADDRESS formulas, and the last one is an optional argument:

  • row_num -- number for the row to use in the cell reference
  • column_num -- numeric value for the column to use in the cell reference
  • abs_num -- [optional] number to specify the type of reference to return
    • 1 - Absolute reference ($A$1)
      • NOTE: Defaults to Absolute reference if this argument is omitted
    • 2 - Absolute row reference, relative column reference (A$1)
    • 3 - Relative row reference, absolute column reference ($A1)
    • 4 - Relative reference (A1)
  • a1 -- [optional] Logical value to specify A1 or R1C1 reference style
    • TRUE - returns A1 style
      • NOTE: Defaults to A1 style if this argument is omitted
    • FALSE - returns R1C1 style
  • sheet_text -- [optional] sheet name can be included, for address on a different sheet
    • NOTE: If this argument is omitted, the address refers to a cell on the formula cell's worksheet

Address00

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

Address01a

Absolute or Relative - abs_num argument

There are 4 options for the abs_num argument:

  1. Absolute - $C$4 - absolute cell reference
  2. Mixed - Absolute row / Relative column - C$4
  3. Mixed - Relative row / Absolute column - $C4
  4. Relative - C4

Address01a

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

Address01b

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 type of reference, 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"

Address01c

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 name of the worksheet, "Ex02", is hard-coded into the formula

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

Address01d

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)

Address01d

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

Address02a

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)

Address03a

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

Address03b

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 suggesting this formula.

The formula examples below show how to do this for:

  1. a specific column number
  2. the cell that contains the ADDRESS formula

1) Column Letter for Specific Column Number

To return the column letter for a specific column number, follow the steps below:

  • In cell A3, you can type a 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","")

Address03b

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.

2) Column Letter for Formula Cell

To make a flexible column letter formula, you can use the COLUMN function, inside the ADDRESS function.

To return the column letter for the cell that contains the ADDRESS formula, follow the steps below:

  • In any cell on the worksheet, type the following formula:
    • =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
  • Press the Enter key, to complete the formula
  • Optional: Select the formula cell, and copy to the right, to show the column letters for additional cells.

In the screen shot below, the formula was entered in cell B4, and the copied across to cell E4.

get column letter for current cell

Note: This formula also returns the correct result for 2-letter columns and 3-letter columns.

get column letter for current cell

How It Works

In this example, the COLUMN function is used without a cell reference.

Because the cell reference argument is omitted, the COLUMN function returns the column number of the current cell.

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 Examples

INDEX and MATCH Functions

CELL Function Examples

TEXT Function Examples

 

 

Last updated: December 26, 2023 2:23 PM