Contents
Yesterday in the marathon 30 Excel functions in 30 days we found the elements of the array using the function MATCH (SEARCH) and found that it works great in a team with other features such as VLOOKUP (VLOOKUP) and INDEX (INDEX).
On the 20th day of our marathon, we will devote the study of the function ADDRESS (ADDRESS). It returns the cell address in text format using the row and column number. Do we need this address? Can the same be done with other functions?
Let’s take a look at the details of the function ADDRESS (ADDRESS) and study examples of working with it. If you have additional information or examples, please share them in the comments.
Function 20: ADDRESS
Function ADDRESS (ADDRESS) returns a cell reference as text based on the row and column number. It can return an absolute or relative link-style address. A1 or R1C1. In addition, the sheet name can be included in the result.
How can the ADDRESS function be used?
Function ADDRESS (ADDRESS) can return the address of a cell, or work in conjunction with other functions to:
- Get cell address given row and column number.
- Find cell value by knowing row and column number.
- Return the address of the cell with the largest value.
Syntax ADDRESS (ADDRESS)
Function ADDRESS (ADDRESS) has the following syntax:
ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])
- abs_num (link_type) – if equal 1 or not specified at all, the function will return the absolute address ($A$1). To get the relative address (A1), use the value 4. Other options: 2=A$1, 3=$A1.
- a1 – if TRUE (TRUE) or not specified at all, the function returns a reference in the style A1, if FALSE (FALSE), then in style R1C1.
- Sheet_text (sheet_name) – the name of the sheet can be specified if you want to see it in the result returned by the function.
Traps ADDRESS
Function ADDRESS (ADDRESS) returns only the address of the cell as a text string. If you need the value of a cell, use it as a function argument INDIRECT (INDIRECT) or use one of the alternative formulas shown in Example 2.
Example 1: Get cell address by row and column number
Using functions ADDRESS (ADDRESS) You can get the cell address as text using the row and column number. If you enter only these two arguments, the result will be an absolute address written in link style A1.
=ADDRESS($C$2,$C$3)
=АДРЕС($C$2;$C$3)
Absolute or relative
If you do not specify an argument value abs_num (reference_type) in a formula, the result is an absolute reference.
To see the address as a relative link, you can substitute as an argument abs_num (reference_type) value 4.
=ADDRESS($C$2,$C$3,4)
=АДРЕС($C$2;$C$3;4)
A1 or R1C1
To style links R1C1, instead of the default style A1, You must specify FALSE for the argument a1.
=ADDRESS($C$2,$C$3,1,FALSE)
=АДРЕС($C$2;$C$3;1;ЛОЖЬ)
Sheet name
The last argument is the name of the sheet. If you need this name in the result, specify it as an argument sheet_text (sheet_name).
=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
=АДРЕС($C$2;$C$3;1;ИСТИНА;"Ex02")
Example 2: Find cell value using row and column number
Function ADDRESS (ADDRESS) returns the address of the cell as text, not as a valid link. If you need to get the value of a cell, you can use the result returned by the function ADDRESS (ADDRESS), as an argument for INDIRECT (INDIRECT). We will study the function INDIRECT (INDIRECT) later in the marathon 30 Excel functions in 30 days.
=INDIRECT(ADDRESS(C2,C3))
=ДВССЫЛ(АДРЕС(C2;C3))
Function INDIRECT (INDIRECT) can work without the function ADDRESS (ADDRESS). Here’s how you can, using the concatenation operator “&“, blind the desired address in the style R1C1 and as a result get the value of the cell:
=INDIRECT("R"&C2&"C"&C3,FALSE)
=ДВССЫЛ("R"&C2&"C"&C3;ЛОЖЬ)
Function INDEX (INDEX) can also return the value of a cell if a row and column number is specified:
=INDEX(1:5000,C2,C3)
=ИНДЕКС(1:5000;C2;C3)
1:5000 are the first 5000 rows of an Excel sheet.
Example 3: Return the address of the cell with the maximum value
In this example, we will find the cell with the maximum value and use the function ADDRESS (ADDRESS) to get her address.
Function MAX (MAX) finds the maximum number in column C.
=MAX(C3:C8)
=МАКС(C3:C8)
Next comes the function ADDRESS (ADDRESS) combined with MATCH (MATCH), which finds the line number, and COLUMN (COLUMN), which specifies the column number.
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))