ADDRESS Function in Excel(Table of Contents)
ADDRESS in Excel
Address Function is a pre-built integrated function in excel that is categorized under Lookup and Reference functions. Address Function in Excel is used to find out the address of a cell in a worksheet. ADDRESS Function in Excel returns or displays the address of a cell based on a given row and column number.
ADDRESS Formula in Excel
Below is the ADDRESS Formula in Excel :
OR
=ADDRESS (row, column, [ref_type], [ref_style], [sheet_name] )
The ADDRESS Function in Excel has the below-mentioned arguments:
- Row or row_num: (Compulsory or required parameter) It is a row number of the cell address.
- Column or Column_num: (Compulsory or required parameter) It is a column number of the cell address.
- [abs_num] or ref_type: (optional argument) It is a numeric value that is specified to obtain below-mentioned reference type.
Note: If this parameter or argument is omitted or not entered, then it returns an Absolute reference of the cell, e.g. $A$1. This argument allows you to select the below mentioned numeric value (1 to 4) based on the output requirement.
Different type of references
| Value | Explanation |
| 1 or Default | Absolute referencing, e.g. $A$1 |
| 2 | Relative column & absolute row, e.g. A$1 |
| 3 | Absolute column & relative row e.g. $A1 |
| 4 | Relative referencing, e.g. A1 |
- [ref_style] or [a1] : (optional argument) It is a logical value indicating whether to use standard (A1 style) cell reference format or R1C1 style cell reference format. This argument allows you to select the below-mentioned ref_style.
If this parameter or argument is omitted or not entered, it will consider the default value 1 or TRUE (A1 style).
Note: Excel has two types of cell reference styles, i.e. A1 & R1C1.
In the A1 reference style, the column name is mentioned as an alphabet, and row name as a number, Whereas in the R1C1 reference style, both row and column are mentioned in numbers.
- [sheet_text]) or [sheet_name]: (optional argument) It is the name of the worksheet to return. Or worksheet name is mentioned in quotes, e.g. “Sales”.
Note: If this argument is omitted or left blank, then no sheet name appears.
How to Use Address Function in Excel?
ADDRESS Function in Excel is very simple and easy to use. Let us understand the working of ADDRESS Function in Excel by some ADDRESS Formula in Excel example.
Example #1
In the below-mentioned example, I have a row number in Cell “B11” & a column number in the cell “C11”. Here I need to find out the cell address with row & column number.
Let’s apply the ADDRESS function in cell “D11”. Select the cell “D11” where the ADDRESS function needs to be applied.
Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “ADDRESS” in the search for a function box, the ADDRESS function will appear in select a function box. Double click on the ADDRESS function.
A dialog box appears where arguments for the ADDRESS function needs to be filled or entered i.e.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
- Row or row_num: (Compulsory or required parameter) It is a row number of the cell address. Here it is 2.
- Column or Column_num: (Compulsory or required parameter) It is a Column number of the cell address. Here it is, 8.
- [abs_num] or ref_type: (optional argument) This argument is omitted or not entered. By default, it will consider 1, i.e. Absolute reference.
- [ref_style] or [a1] : (optional argument) This argument is omitted or not entered. By default, it will consider 1 or TRUE (A1 style).
- [sheet_text]) or [sheet_name]: (optional argument) this argument is omitted or not entered, it is left blank. By default, no sheet name appears.
Click ok, after entering all the ADDRESS function arguments.
=ADDRESS(B11,C11) or =ADDRESS(2,8)
When the above parameters or arguments i.e. ([abs_num], [a1], [sheet_text]) is not mentioned, then ADDRESS function results in or returns a pattern of absolute address with row and column name (i.e. $H$2).
Here, $H indicates the Absolut column (8), whereas $2 indicates the Absolute Row (2).
Example #2
In the below-mentioned example, I have a row number in Cell “B16” & column number in the cell “C8” & Reference type or abs_num as 3 in cell “D16”. Here I need to find out the cell address & it should reflect as Absolute Column & Relative Row in Cell Address.
Let’s apply the ADDRESS function in cell “E16”. Select the cell “E16”. where the ADDRESS function needs to be applied.
Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “ADDRESS” in the search for a function box, ADDRESS function will appear in select a function box. Double click on the ADDRESS function.
A dialog box appears where arguments for the ADDRESS function needs to be filled or entered i.e.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
- Row or row_num: (Compulsory or required parameter) It is a row number of the cell address. Here it is 2.
- Column or Column_num: (Compulsory or required parameter) It is a column number of the cell address. Here it is, 8.
- [abs_num] or ref_type: (optional argument) Here in the cell address, output to be returned as absolute column & relative row in cell address. Therefore, we have to enter 3.
- [ref_style] or [a1] : (optional argument) This argument is omitted or not entered. By default, it will consider 1 or TRUE (A1 style).
- [sheet_text]) or [sheet_name]: (optional argument) this argument is omitted or not entered, it is left blank. By default, no sheet name appears.
Click ok after entering all the ADDRESS function arguments. i.e.
=ADDRESS(2,8,3)
ADDRESS function results in or returns a pattern of absolute column & relative row in cell address (i.e. $H2).
Here, $H indicates the Absolut column, whereas 2 indicates the relative Row (2).
Things to Remember
#VALUE! error occurs if the row or column number argument is less than 1. If the Column number is greater than the number of columns in excel (16,384 Columns). If the row number is greater than the number of rows in excel (10,48,576 rows).
In ADDRESS function argument, if row_num, column_num or [abs_num] is nonnumeric value then it returns #VALUE! error.
Recommended Articles
This has been a guide to ADDRESS in Excel. Here we discuss the ADDRESS Formula in excel and How to use the ADDRESS Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
