- Advanced Excel Functions Tutorial
- Advanced Excel Functions - Home
- Compatibility Functions
- Advanced Excel Functions - Cube
- Database Functions
- Date & Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Advanced Excel Functions - Logical
- Lookup & Reference Functions
- Math & Trignometric Functions
- Statistical Functions
- Useful Resources
- Quick Guide
- Useful Resources
- Discussion
Lookup and Reference - ADDRESS Function
Description
You can use the ADDRESS function to obtain the address of a cell in a worksheet, given specified row and column numbers.
For example, ADDRESS (2,3) returns $C$2. As another example, ADDRESS (77,300) returns $KN$77. You can use other functions, such as the ROW and COLUMN functions, to provide the row and column number arguments for the ADDRESS function.
Syntax
ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])
Arguments
| Argument | Description | Required/ Optional |
|---|---|---|
| row_num | A numeric value that specifies the row number to use in the cell reference. | Required |
| column_num | A numeric value that specifies the column number to use in the cell reference. | Required |
| abs_num | A numeric value that specifies the type of reference to return. See the Table given below. |
Optional |
| a1 | A logical value that specifies the A1 or R1C1 reference style. In A1 style, columns are labeled alphabetically, and rows are labeled numerically. In R1C1 reference style, both columns and rows are labeled numerically. If the A1 argument is TRUE or omitted, the ADDRESS function returns an A1-style reference. If the A1 argument is FALSE, the ADDRESS function returns an R1C1-style reference. |
Optional |
| sheet_text | A text value that specifies the name of the worksheet to be used as the external reference. For example, the formula = ADDRESS (1,1,,,"Sheet2") returns Sheet2!$A$1. If the sheet_text argument is omitted, no sheet name is used, and the address returned by the function refers to a cell on the current sheet. |
Optional |
abs_num argument returns the following values −
| abs_num | Returns this type of reference |
|---|---|
| 1 or omitted | Absolute |
| 2 | Absolute row; relative column |
| 3 | Relative row; absolute column |
| 4 | Relative |
Notes
If the row_num is less than 1 or greater than the number of rows in the spreadsheet, ADDRESS Functions returns #VALUE error.
If the column_num is less than 1 or greater than the number of columns in the spreadsheet, ADDRESS Functions returns #VALUE error.
If any of the supplied row_num, column_num or [abs_num] arguments are nonnumeric, ADDRESS Functions returns #VALUE error.
If the supplied [a1] argument is not recognized as a logical value, ADDRESS Functions returns #VALUE error.
Applicability
Excel 2007, Excel 2010, Excel 2013, Excel 2016
Example
