The Excel ADDRESS function generates a cell address. The result is the row and column number in text format.
For example, =ADDRESS(2,2) returns $B$2. Depending on the arguments, the ADDRESS function returns the cell address in the absolute, relative, or mixed cell reference.
Syntax
=ADDRESS(row_num, col_num, [abs_num], [a1], [sheet])Arguments
The function uses two required and three optional arguments.
- row_num: the row number that you refer to.
- col_num: the column number that you refer to.
- abs_num: You can specify the address type you want to use (relative or absolute).
- a1 [optional]: Determine the reference type (set 1 to use A1 format or set 0 to use R1C1 format)
- sheet [optional]: Worksheet name
ADDRESS Function Examples
The ADDRESS function gets the address of a selected cell based on a row and column number, and with its help, you can build relative, absolute references. Good to know that the returned value (cell reference) is a text value.
Using the cell reference inside a formula is tricky, and you must apply an additional “&” character. The “&” character in Excel joins two or more text values.
=A1 & " " & B1Another example is that if you want to create a numeric array, use the following formula:
=ROW(INDIRECT("1:5"))The formula will create an array that contains numeric values between 1 and 5, like {1;2;3;4;5}
Using the ADDRESS function ABS argument
As we stated above, the “abs” is an optional argument.
- 1: Absolute reference, for example, $B$4
- 2: Mixed reference, absolute row, relative column: B$4
- 3: Mixed reference, absolute column, relative row: $B4
- 4: Relative reference, for example, B4
Here are a few examples of how to use the third argument of the ADDRESS function:
=ADDRESS(2,2) = $B$2
=ADDRESS(2,2,4) = B2
=ADDRESS(2,2,3) = $B2
=ADDRESS(2,2,2) = B$2
=ADDRESS(2,2,1,FALSE) = R2C2
=ADDRESS(2,2,4,TRUE,"Sheet3") = Sheet3!B2Summary
You can use the ADDRESS function to create a cell reference for a formula or other functions. Furthermore, combining the function with another lookup function (for example, INDEX or MATCH) is easy to create cell references even if you want to use specific criteria. It also provides optional arguments and allows using a reference style (A1 or R1C1-style), which helps you avoid errors.
