Address function in excel is used to find the address of the cell and the value returned by this function is absolute, it is an inbuilt function, this function has two mandatory arguments which is the row number and the column number, for example, if we use =Address(1,2) we will get output as $B$1.
Syntax:= ADDRESS (row_num, col_num, [abs_num], [a1], [sheet])
The ADDRESS function syntax has the following arguments:
- row_num Required. A numeric value that specifies the row number to use in the cell reference.
- column_num Required. A numeric value that specifies the column number to use in the cell reference.
- abs_num Optional. A numeric value that specifies the type of reference to return.
| abs_num | Returns this type of reference |
|---|---|
| 1 or omitted | Absolute |
| 2 | Absolute row; relative column |
| 3 | Relative row; absolute column |
| 4 | Relative |
- A1 Optional. A logical value that specifies the A1 or R1C1 reference style. In A1 style, columns are labeled alphabetically, and rows are labeled numerically. In R1C1 reference style, both columns and rows are labeled numerically. If the A1 argument is TRUE or omitted, the ADDRESS function returns an A1-style reference; if FALSE, the ADDRESS function returns an R1C1-style reference.
- sheet_text Optional. A text value that specifies the name of the worksheet to be used as the external reference. For example, the formula =ADDRESS(1,1,,,”Sheet2″) returns Sheet2!$A$1. If the sheet_text argument is omitted, no sheet name is used, and the address returned by the function refers to a cell on the current sheet.
Example: Let’s look at some Excel ADDRESS function examples and explore how to use the ADDRESS function as a worksheet function in Microsoft Excel:
Syntax: =ADDRESS(A2,B2,C2)
Result:
Based on the Excel spreadsheet above, the following ADDRESS examples would return:
Syntax: =ADDRESS(A3,B3,C3)
Result: A$1
Syntax: =ADDRESS(A4,B4,C4)
Result: $A1
Syntax: =ADDRESS(A5,B5,C5)
Result: A1
Syntax: =ADDRESS(A6,B6)
Result: $O$12
Syntax: =ADDRESS(A7,B7)
Result: $E$100
Syntax: =ADDRESS(A8,B8,C8,FALSE)
Result: R2C[3]
Syntax: =ADDRESS(A8,B8,C8,FALSE,”Cost”)
Result: Cost!R2C[3]
Note:
The ADDRESS function is used to create an address from a given row and column number.
Depending upon the need set the Absolute parameter to one of the following
- 1 or omitted, Absolute reference
- 2, Absolute row; relative column
- 3, Relative row; absolute column
- 4, Relative reference
Do not forget to set the second parameter i.e. reference type of the INDIRECT function to zero or False when Reference style is the R1C1 type.
