ADDRESS function

In this lesson you can learn how to use ADDRESS function. By using the ADDRESS function, you can get the address of the cell in the spreadsheet, giving a specific row and column number.

Address function creates a text based on the address of cell in the spreadsheet.

The syntax is as follows:

ADDRESS (row_num; column_num, [abs_num], [A1], [sheet_text])

  • row_num and column_num are arguments which specify the location of the cell. For example, to create a text cell reference C2 are, respectively, the number of type 2 and 3
  • abs_num is a kind of appeal to create a function, given as a number between 1-4, as follows:

1 - (default) means the absolute reference ($A$1),

2 - row absolute, relative to the column (A$1),

3 - relative to the row, absolute for the column ($A1),

4 - relative reference (A1).

  • A1 is an optional argument allows us to choose the type of appeal.

If you leave the field blank or write in the TRUE or 1, the address is created in the style of A1,

if the argument will be set to false or 0 - R1C1 style address.

  • sheet_text is an optional argument allows you to specify the sheet that contains the cell. You must write the name of the sheet in quotation marks.

 

Examples of Address function in Excel

=ADDRESS(2,3) returns $C$2

=ADDRESS(2,3,1) returns $C$2

=ADDRESS(2,3,2) returns C$2

=ADDRESS(2,3,3) returns $C2

=ADDRESS(2,3,4) returns C2

=ADDRESS(2,3,1,TRUE) returns $C$2

=ADDRESS(2,3,1,FALSE) returns R2C3

=ADDRESS(2,3,1,TRUE,"Sheet4") returns Sheet4!$C$2

Example 1 Find MAX value in range

You have values in A1:A5 range. You want to find the address of cell with the greatest value. Formula is:

=ADDRESS(MATCH(MAX(A1:A5),A1:A5,0),1,4)

Excel MAX value range

Example 2 Find MIN value in range

You have values in A1:A5 range. You want to find the address of cell with the smallest value. Formula is:

=ADDRESS(MATCH(MIN(A1:A5),A1:A5,0),1,4)

Excel MIN value range