How to use the Excel ADDRESS function

Summary

The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return an address in relative, mixed, or absolute format, and can be used to construct a cell reference inside a formula.

Purpose

Create a cell address from a row and column number

Return value

A cell address in the current or given worksheet.

Syntax

=ADDRESS (row_num, col_num, [abs_num], [a1], [sheet])

Arguments

row_num - The row number to use in the cell address.
col_num - The column number to use in the cell address.
abs_num - [optional] The address type (i.e. absolute, relative). Defaults to absolute.
a1 - [optional] The reference style, A1 vs R1C1. Defaults to A1 style.
sheet - [optional] The name of the worksheet to use. Defaults to current sheet.

Version

Excel 2003

Usage notes

The ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return a relative, mixed, or absolute reference, and can be used to construct a cell reference inside a formula. It's important to understand that the ADDRESS returns a reference as a text value. If you want to use this text inside a formula reference, you will need to coerce the text to a proper reference with the INDIRECT function. If you want to provide a row and column number and get back the value at that address, use the INDEX function.

The ADDRESS function takes five arguments: row, column, abs_num, a1, and sheet_text. Row and column are required, other arguments are optional. The abs_num argument controls whether the address returned is relative, mixed, or absolute, with a default value of 1 for absolute. The a1 argument is a Boolean that toggles between A1 and R1C1 style references with a default value of TRUE for A1 style references. Finally, the sheet_text argument is meant to hold a sheet name that will be prepended to the address.

ABS options

The table below shows the options available for the abs_num argument for returning a relative, mixed, or absolute address.

abs_num	Result
1 (or omitted)	Absolute ($A$1)
2	Absolute row, relative column (A$1)
3	Relative row, absolute column ($A1)
4	Relative (A1)

Examples

Use ADDRESS to create an address from a given row and column number. For example:

=ADDRESS(1,1) // returns $A$1
=ADDRESS(1,1,4) // returns A1
=ADDRESS(100,26,4) // returns Z100
=ADDRESS(1,1,1,FALSE) // R1C1
=ADDRESS(1,1,4,TRUE,"Sheet1") // returns Sheet1!A1