- 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.
How to use
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. Note that 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.
Note: ADDRESS is a special purpose function and is not necessary in most formulas. For example, to retrieve a value at a specific row and column location, you can use INDEX and MATCH.
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.
The table below shows the options available for the abs_num argument for returning a relative, mixed, or absolute address.
|1 (or omitted)||Absolute ($A$1)|
|2||Absolute row, relative column (A$1)|
|3||Relative row, absolute column ($A1)|
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