The Excel ADDRESS function is a Lookup & Reference formula that returns the address of a cell based on its input arguments. The function also allows you to set the type and reference style of the address. In this guide, we’re going to show you how to use the Excel ADDRESS function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Excel ADDRESS Function Syntax
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
Arguments
| row_num | An integer that specifies the row number of the cell you want to find the address for. |
| column_num | An integer that specifies the column number of the cell you want to find the address for. |
| [abs_num] |
Optional. A numeric value that specifies the type of reference.
|
| [a1] |
Optional. A logical value that specifies the reference style.
|
| [sheet_text] | Optional. A text value that specifies the name of the worksheet. By default, the reference returns without a sheet name. |
Examples
The Excel ADDRESS function requires two arguments to execute: row_num and column_num. Each argument specifies one of the coordinates for the target reference for which you want to find the address. The remaining three are optional arguments that configure the reference style.
The first optional argument, [abs_num] defines the absolute/relative type of the reference. You can simply omit it, or give it a number between 1 and 4 to set the type of the reference. Now, let's take a closer look at how the function works.
A1-reference (Default style)
The A1 reference system is Excel's default system, and it is the default configuration for the ADDRESS function as well. If you leave the [a1] argument empty or set it to TRUE, you will get the address in A1-style.
=ADDRESS(2,4,4)
=ADDRESS(2,4,,TRUE)
=ADDRESS(2,4,4,1) //1 means TRUE, 0 means FALSE
R1C1-reference
R1C1 reference style works very well with relative references. Instead of coordinates like B5, R1C1 displays the relative position of referenced ranges. If you are using R1C1 reference style in your workbook, you need to set the [a1] argument as FALSE or 0.
=ADDRESS(2,4,4,0) //1 means TRUE, 0 means FALSE
Using the Excel ADDRESS Function with sheet name
The last optional argument is [sheet_name]. Supply a text value to get a reference that contains the sheet name. If the argument is omitted, the reference returns without a sheet name.
Tips
- Combine the Excel ADDRESS function with the INDIRECT function to create dynamic references. For example, the following formula targets a range which size is determined by the values in the cells A1, A2, A3 and A4.INDIRECT(ADDRESS(A1,A2)&":"&ADDRESS(A3,A4))
