The ADDRESS function takes row and column numbers and returns a cell reference as a text string.

Syntax

=ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])

Arguments

Argument Description
row_num A numeric value that specifies the row number to use in the cell reference
column_num A numeric value that specifies the column number to use in the cell reference
[abs_num] Optional. A numeric value that specifies the type of reference to return

  [abs_num] Returns this type of reference
  1 (or omitted) Absolute
  2 Absolute row; relative column
  3 Relative row; absolute column
  4 Relative
[a1] Optional. A logical value that specifies the A1 or R1C1 reference style

  True – denotes A1 reference style
  False – denotes R1C1 reference style

If [a1] is omitted, the argument takes on the default value “True”

[sheet_text] Optional. A text value that specifies the name of the worksheet to be used as the external reference

  If [sheet_text] argument is omitted, the sheet name is blank, i.e. the reference relates to the current worksheet

Note: 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.

Examples

  A B C
1 Formula Result Notes
2 =ADDRESS(2,3) $C$2 Absolute reference
3 =ADDRESS(2,3,2) C$2 Absolute row; relative column
4 =ADDRESS(2,3,2,FALSE) R2C[3] Absolute row; relative column in R1C1 reference style
5 =ADDRESS(2,3,1,FALSE,”[Book1]Sheet1″) ‘[Book1]Sheet1’!R2C3 Absolute reference to another workbook and worksheet in R1C1 reference style
6 =ADDRESS(2,3,1,FALSE,”EXCEL SHEET”) ‘EXCEL SHEET’!R2C3 Absolute reference to another worksheet in R1C1 reference style

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  the row_num is less than 1 or greater than the number of rows in the spreadsheet
  the column_num is less than 1 or greater than the number of columns in the spreadsheet
  any of the supplied row_num, column_num or [abs_num] arguments are non-numeric
  the supplied [a1] argument is not recognised as a logical value