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.

  • row_num – In this argument, enter the row number.
  • column_num – In this argument, specify the column number.
  • [abs_num] – In this optional argument, specify the reference type to return (i.e. absolute or relative). It accepts four values.
    • 1 – Absolute row and column. Eg – $A$4 (default one)
    • 2 – Absolute row and relative column. Eg – A$4
    • 3 – Absolute column and relative row. Eg – $A4
    • 4 – Relative row and column. Eg – A4
  • [a1] – In this optional argument, specify the reference style which is either R1C1 or A1. It accepts two values:
    • 0 or FALSE – R1C1 style
    • 1 or TRUE – A1 style (default one)
  • [sheet_text] – In this optional argument, specify the sheet name (as a text value within double) that you want to include in the cell address reference. If you leave this argument blank, excel would not enter any sheet name for the cell address. It will assume current active worksheet, by default.

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.

Basic Example of ADDRESS Function

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:

  • 1 – Absolute row and column. Eg – $A$4 (default one)
  • 2 – Absolute row and relative column. Eg – A$4
  • 3 – Absolute column and relative row. Eg – $A4
  • 4 – Relative row and column. Eg – A4

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

=ADDRESS(6,5,4)
Return relative cell address in Excel

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

Using abs_num argument ADDRESS function

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.

  • 0 or FALSE – R1C1 style
  • 1 or TRUE – A1 style (default one)
=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.

R1C1 Cell Address Style

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

Relative Cell Reference of R1C1 style

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.

Infographic - ADDRESS Formula Function in Excel

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:

Return Cell Address with Sheet Name

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

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

List of Cities in Excel

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’.

Using INDIRECT with ADDRESS Function Excel

Thank You 🙂

Leave a Comment