ADDRESS
Formulas / ADDRESSReturn the address of a cell using a row and column number.
=ADDRESS(row_num, col_num, [abs_num], [a1], [sheet])
- row_num - numeric value representing row number
- column_num - numeric value representing column number
- abs_num - [OPTIONAL] numeric value that specifies whether to return an absolute reference or not (defaults to 1)
- A1 - [OPTIONAL] boolean that determines if A1 or R1C1 reference style is used
- sheet_text - [OPTIONAL] text string that determines the worksheet name to use
Examples
=ADDRESS(1,1,4,TRUE,"Sheet1")The ADDRESS function can be used to convert a row and column number into an address. The formula above, will return Sheet1!A1. This means that the cell in the first row and first column on Sheet1 will be returned as the address.
=ADDRESS(1,1)The ADDRESS function can also be used to return an address without the sheet name. For example,the formula above will return $A$1. This means that the cell in the first row and first column will be returned as the address.
=ADDRESS(1,1,4)The ADDRESS function can also be used to specify the format of the address. For example, the formula above will return A1. This means that the cell in the first row and first column will be returned as the address in the A1 formatting.
=ADDRESS(100,26,4)The ADDRESS function can also be used to return an address with a larger row and column number. For example, this example formula will return Z100.
Summary
The ADDRESS function returns the address of a cell in a worksheet given the row and column numbers specified. It can also generate either an absolute or relative reference depending on the abs_num argument.
- The ADDRESS function returns a cell address by using a given row and column number, and can return addresses in relative, mixed, or absolute formats.
- The ADDRESS function can construct a cell reference within a formula, and outputs a text value.
- The text that ADDRESS returns must be converted into a proper reference to use it in a formula, which can be done using the INDIRECT function.
- The INDEX function can be used to get the value at an address returned by ADDRESS.
Frequently Asked Questions
What is the ADDRESS function?
What arguments does the ADDRESS function require?
What are the return types for the ADDRESS function?
