Excel ADDRESS function
- Example 1-Basic usage: Obtain cell address from given column and row
- Example 2-Cell value from row and column number
- Example 3-Get address of max value
- Example 4-Return column letter based on column number
The ADDRESS function returns the cell address reference as text, based on the given column number and row number. For example, the formula =ADDRESS(1,1) returns $A$1. The ADDRESS function can return relative or absolute address, and, return in A1 or R1C1 style, also, the sheet name can be included in the result.
|ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])|
|Abs_num argument ||The type of reference||Example|
|1 or omitted||Absolute row and column||$A$1|
|2||Relative row, absolute column||A$1|
|3||Absolute row, relative column||$A1|
|4||Relative row and column||A1|
|1 or TRUE or omitted||A1||Columns are labeled alphabetically, and rows are labeled numerically|
|0 or False||R1C1||Both columns and rows are labeled numerically|
ADDRESS function returns a cell reference as text.
Here some examples are provided to explain how to use the INDEX function.
1) If you just enter the row and column arguments in the ADDRESS function,
A2 and B2 are the numeric values of row and column, and it returns
2) If you enter the row, column and abs arguments in the ADDRESS function,
C3 is the abs arguments, 2 indicates to display reference as relative row and absolute column, and it returns
3) If the fourth argument is entered in the ADDRESS function,
D4 controls the reference style, A1 or R1C1, 0 or False will display result in A1 style, 1 or True will show result in R1C1 style, here it returns
4) If all the arguments are entered in the ADDRESS function,
E6 is the fifth argument which indicates the sheet refer to, it returns
The ADDRESS function returns the cell address as text, if you want to show the cell value in the cell address, you can to combine the ADDRESS function and the INDIRECT function to achieve this goal.
Here is a formula in B4 which will obtain the cell value in B1.
In this case, I introduce how to use the ADDRESS function to get the cell address of max value.
Firstly, you need to get the max value with this formula =MAX(B2:B6).
Then use the formula
MATCH(E1,B1:B6,0) will find the row number, E1 is the max value, B1:B6 is the column you find max value from;
COLUMN(B1) will find the column number, B1 is the column you find value from.
Note: This formula can only find the max value in one column.
In this example, I will introduce how to use the ADDRESS function to return column letter based on a given column number.
For example, you want to get the column letter for the 29th column, use below formula:
What the arguments mean:
ADDRESS function: 1 is row 1, A3 is the column number whose relative column letter you want to get, 4 is abs argument which returns reference in relative, in this part, the ADDRESS function gets result AC1;
SUBSTITUTE function: replace 1 with empty string, so the final result is
If you want to get the column letter of current cell, you can use this formula