Site icon Excel Unlocked

ADDRESS Function in Excel – Get Excel Cell Address

In this tutorial, we would how to use the ADDRESS function in excel to return the cell address using row and column numbers. We would also look at some excellent examples to understand using the ADDRESS excel formula.

Here we go 😎

When To Use ADDRESS Function in Excel

The excel’s ADDRESS function is a useful formula when you want to return the address of a cell using the row number and column number. It returns a text string.

Syntax and Arguments

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

The ADDRESS function has five arguments. The first two arguments are mandatory ones and the last three are optional.

Examples of ADDRESS Function in Excel

Let us now see how the ADDRESS Function works in excel with the help of some examples (from basic to advanced).

Ex. 1 – Using Mandatory Arguments of ADDRESS Formula – Get Cell Address

To return the absolute cell address (i.e. with a dollar sign) of cell E6, simply use the following formula:

=ADDRESS(6,5)

As a result, excel returns the output as $E$6. In the above formula syntax, 6 represents row number and 5 represents column number of column E.

Similarly, the formula =ADDRESS(10,9) would return $I$10.

Note that, by default, the formula returns the absolute cell reference (i.e. with dollar sign before row and column value).

To return a relative or a mixed reference of the cell, see example 2 in the below section.

Ex. 2 – Using ADDRESS Function to Return Mixed or Relative Reference

To return the mixed or relative reference of a cell in excel, simply use the third argument viz. [abs_num] as per the bullet points below:

To return a relative cell address of cell E6, use the below formula:

=ADDRESS(6,5,4)

Similarly, the formula =ADDRESS(6,5,2) and =ADDRESS(6,5,3) will return mixed cell address, as shown in the image below:

Ex. 3 – Return R1C1 Format Cell Address in Excel Cell

By default, the ADDRESS excel function always returns the A1 format cell reference style. To return the R1C1 style of cell reference, use the [a1] argument of the ADDRESS formula.

=ADDRESS(6,5,1,0)

OR

=ADDRESS(6,5,1,FALSE)

As a result, the above formula would return the R1C1 style cell reference – R6C5. The text R6C5 denotes a cell with the intersection of row 6 and column 5.

The relative cell reference of an R1C1 cell style is R[1]C[1]. See the image below:

Ex. 4 – Get Cell Address with Worksheet Name

To get the cell address with a custom sheet name, enter the required sheet’s name (within double quotes) as the fifth argument of the excel ADDRESS function.

Support you want to return the cell address for row 6 and column 5 with sheet name as “My Sheet”. Use the below formula:

=ADDRESS(6,5,,,"My Sheet")

As a result, excel would return the cell address with the specified sheet name as shown in the image below:

Ex. 5 – Get Particular Cell’s Value Using Cell Address

Suppose you have a list of cities in column A of an excel worksheet.

Now, you want to return a specific city name based on the cell address in another cell.

To achieve this, simply use the following value:

=INDIRECT(ADDRESS(4,1))

As a result, excel would return the output – ‘Bangalore’.

Thank You 🙂

Exit mobile version