ADDRESS Function In Excel

What Is ADDRESS Function In Excel?

The ADDRESS Function in Excel provides the cell address or cell reference of a selected cell w.r.t the specified row and column. We can combine it with other functions to directly refer to a cell. The function will return the cell address as a text string.  

The ADDRESS Function in Excel is an inbuilt “Lookup and Reference” function, so we can enter the formula directly in the worksheet or insert it from the “Function Library”.

For example, we will apply the ADDRESS Function in Excel to get the cell’s address.

Select cell A1, enter the formula =ADDRESS(1,1), and press the “Enter” key.

The output is “$A$1”, as shown above, i.e., the address is 1^st Row in 1^st Column.

ADDRESS() Excel Formula

The Syntax of the ADDRESS Excel formula is,

The arguments of the ADDRESS Excel formula are,

• row_num: It is the mandatory argument. It is a number/value that represents the row number.
• column_num: It is the mandatory argument. It is a number/value that represents the column number.
• abs_num: It is an optional argument. It is a number/value that represents the type of reference to return, whether absolute, relative, or mixed, as follows:

abs_num	Explanation
1	Absolute Reference, e.g., “$A$1.”
2	Relative Column Reference & Absolute Row Reference, e.g., “A$1.”
3	Absolute Column Reference & Relative Row Reference, e.g., “$A1.”
4	Relative Reference, e.g., “A1.”

• a1: It is the optional argument. It is a logical value where both the columns and rows are labeled numerically, or the cell address is given. It can either be TRUE [A1 style] or FALSE [R1C1 style]. By default, it will return TRUE.
• sheet_text: It is the optional argument. It is the worksheet name of the target cell. By default, it will take the current worksheet.

How to Use ADDRESS Excel Function?

We can use the ADDRESS function in Excel in 2 methods, namely,

1. Access from the Excel ribbon.
2. Enter in the worksheet manually.

Method #1 – Access from the Excel ribbon

First, choose an empty cell for the output → select the “Formulas” tab → go to the “Function Library” group → click the “Lookup & Reference” drop-down → select the “ADDRESS” function as shown below.

The “Function Arguments” windowappears. Enter the value in the “row_num”, “column_num”, “abs_num”, “a1”, and “sheet_text” fields, accordingly, and click “OK”, as shown below.

Method #2 – Enter in the worksheet manually

1. Select an empty cell for the output.
2. Type =ADDRESS( in the selected cell. [Alternatively, type =A and double-click the ADDRESS function from the list of suggestions shown by Excel.]
3. Enter the arguments accordingly and close the brackets.
4. Press the “Enter” key.

We will calculate the cell addresses using a basic example.

In the table, the data is,

• Column A contains the Row_num.
• Column B contains the Column_num.
• Column C contains the abs_num.
• Column D contains the a1.
• Column E contains the Sheet_text.
• Column F displays the Output.

Examples

We will understand some advanced scenarios by using ADDRESS function in Excel.

Example #1

We will calculate the cell addresses using ADDRESS function in Excel.

In the table, the data is,

• Column A contains the Row_num.
• Column B contains the Column_num.
• Column C displays the Output.

The steps to get the cell address by using the ADDRESS Function in Excel are as follows:

• 1: Select cell C2 and enter the formula =ADDRESS(A2,B2).

• 2: Press the “Enter” key. The result is “$A$4”, as shown below.

• 3: Drag the formula from cell C2 to C5 using the fill handle.

The output is shown above. Only the mandatory argument inputs return the default style output.

Example #2

We will get the cell addresses using the ADDRESS() and the INDIRECT function.

[Note: The INDIRECT function gets the value stored in the cell address. We get the cell reference from the ADDRESS function and the actual value through a reference from the INDIRECT function.]

In the table, the date is,

• Column A contains the Fruits.
• Column B contains the Price.
• Column E displays the Output.

The procedure to find the value using the INDIRECT() and the ADDRESS() functionsare as follows:

• First, select cell E2, enter the formula =INDIRECT(ADDRESS(4,2)), and press the “Enter” key.

The output is “$200”, as shown above. The cell address is 4^th row, 2^nd column, and the value is “$200” that the INDIRECT function returned.

Example #3

We will calculate the first and second cell address values using the ADDRESS Function in Excel along with the ROW() function and COLUMN() function.

In the table, the data,

• Column A contains the Items.
• Column B contains the Price.
• Column E displays the Output.

The steps to find the value by the ADDRESS Function in Excel are as follows:

• Step 1: Select cell E2, enter the formula =ADDRESS(ROW(B3:B5),COLUMN(B3:B5)), and press the “Enter” key. The result is “$B$3”, as shown below.

• Step 2: Select cell E3, enter the formula =ADDRESS(ROW(B3:B5)+ROWS(B3:B5)-1,COLUMN(B3:B5)+COLUMNS(B3:B5)-1), and press the “Enter” key. The output is “$B$5”, as shown below.

[Note: In the E3 cell’s formula, the row_num is ROW(B3:B5)+ROWS(B3:B5)-1 and the column_num as COLUMN(B3:B5)+COLUMNS(B3:B5)-1.]

Important Things to Note

• The “abs_num” value is set according to the following parameters:
  • 1 – Absolute reference in excel
  • 2 – Absolute row, relative column
  • 3 – Relative row, absolute column
  • 4 – Relative reference in excel
• The reference style can be True or False. Now, the default reference style of the function is “A1” if no value is entered for the argument.
• To change the cell reference style from the usual Excel style,
  • Select File → Options, to open the “Excel Options” window.
  • On the left side of the “Excel Options” window, select “Formulas” from the list.
  • On the right side, in the “Working with formulas” group, check/tick the “R1C1 reference style” checkbox, as shown below.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the ADDRESS Function in Excel’s formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to ADDRESS Function In Excel. Here we explain how to use ADDRESS Formula along with examples & downloadable excel template. You can learn more from the following articles –

• Track changes in Excel
• MEDIAN Excel Function
• SMALL Function In Excel