Site icon Best Excel Tutorial

How to Use Address Function in Excel

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.

What is the ADDRESS function in Excel?

ADDRESS stands for “Address”. It is a function that takes three arguments:

For example, if you want to return the address of the cell in row 2 and column 3, you would use the following formula: =ADDRESS(2, 3)

This formula would return the address “$B$2”.

Address function syntax

The syntax is as follows:

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

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).

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.

How does the ADDRESS function work?

The ADDRESS function works by first converting the row and column numbers into text strings. The text strings are then concatenated together with the $ symbol to create the absolute address. If the optional Boolean value is TRUE, the address is returned as a relative address.

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

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)

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)

Exit mobile version