The ADDRESS Function in Excel

The ADDRESS function simply returns the address of a cell by using row number and column number.
188
Syntax of ADDRESS Function

=ADDRESS(row_num,col_num,[abs_num],[a1],[sheet_name])

Row_num: The Row number

Col_num: The Column number

[abs_num]: Optional Argument. Has four possible inputs, 1, 2, 3, and 4. If omitted or 1, returned address will be absolute eg $A$1. 2 returns absolute row and relative column eg A$1.3 returns relative row and absolute column $A1. 4 returns relative address eg A1.
[a1]: Optional Argument. Has two possible inputs, 0 and 1. If omitted, it will return A1-style . 0 returns output in R[1]C[1] style.
[sheet_name]: Optional Argument. If sheet name is provided, add it with address along ! mark. Eg. mysheet!A1.
IMPORTANT: The address returned is in text format.
Now lets have some example:
Excel ADDRESS Example
189

    1. Get relative address of Row 3 and Column 1.
=ADDRESS(3,1,4)

Above ADDRESS formula will return A3.

    1. Get Value of a cell using ADDRESS Function
=INDIRECT(ADDRESS(3,1))

Above formula will return value at $A$3

    1. Get ADDRESS Of Max Value in a Range.

We have this table.
190
Write this formula anywhere to get address of Max Value.

=ADDRESS(MATCH(MAX(B:B),B:B,0),COLUMN(B2))

MAX function finds max value in range B:B
MATCH looks for that MAX value in B:B range and returns index or say row number.
COLUMN returns column number of B2.
Now, ADDRESS has row_num and col_num. And this all it needs.

    1. Get adjacent cells value of Max Value

Many times you woul require to get the name of a person getting max or min value. One approach is this.

=OFFSET(INDIRECT(ADDRESS(MATCH(MAX(B:B),B:B,0),COLUMN(B2))),0,-1)

Since address returned by ADDRESS function is text, we use INDIRECT so that OFFSET function takes it as real address.
we move 1 column left to max cell using -1. And finally offset returns name of robo that is getting max pay.

So, I explained ADDRESS function here and gave some example in which ADDRESS is most useful. But as we know everyone’s need is different, let me know your need in the comments section below. We will get it together.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube