How to Use Excel ADDRESS Function (7 Examples)

The ADDRESS function in MS Excel is under the Lookup and Reference functions category. We use the ADDRESS function to get the address of a cell (e.g. A45) in an Excel worksheet in text form given specified the corresponding row and column numbers. The ADDRESS function can return the cell address in Absolute, Mixed, or Relative reference format (e.g. $A$45, A$45, $A45, A45, etc.) with corresponding input. The address can also be used as the cell reference inside another formula.
The following picture is an overview of the uses of the ADDRESS function.

Overview of the Uses of Excel ADDRESS function

In this article, we will learn how we can use the ADDRESS function effectively in Excel with suitable examples and proper illustrations.


Download Practice Workbook

Click on the following button and download the sample workbook to practice along with it.


Introduction to the ADDRESS Function

The ADDRESS Function in Excel

Function Objective:

The ADDRESS function is used to create a cell reference as text, given specified the row and column numbers.

Syntax:

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

Arguments:

Argument Required/Optional Explanation
row_num Required This refers to the row number in the cell reference.
column_num Required This refers to the column number in the cell reference.
abs_num Optional This specifies the type of cell reference to return.
  • 1 or omitted– Absolute reference
  • 2– Absolute row, relative column
  • 3– Relative row, absolute column
  • 4– Relative reference
a1
Optional This is a logical value that specifies the A1 or R1C1 reference style.
  • 1 or TRUE or omittedA1 reference style. Example: B5.
  • 0 or FALSER1C1 reference style. Example: R5C[2].
sheet_text
Optional This indicates the sheet address of the reference. If this argument is omitted, it refers that the cell being on the current sheet. Example: ‘Sheet1’R5C[2].

Use of Required and Optional Arguments:

Formula Description Output
=ADDRESS(1,1,1,0,”[Book1]Sheet1″) Absolute reference to another worksheet in another workbook in R1C1 style ‘[Book1]Sheet1’!R1C1
=ADDRESS(1,1,2,0,”[Book1]Sheet1″) Absolute row; relative column to another worksheet in another workbook in R1C1 style ‘[Book1]Sheet1’!R1C[1]
=ADDRESS(1,1,4,0,”[Book1]Sheet1″) Relative reference to another worksheet in another workbook in R1C1 style ‘Book1]Sheet1’!R[1]C[1]
=ADDRESS(1,1,1,1,”[Book1]Sheet1″) Absolute reference to another worksheet in another workbook in A1 style ‘[Book1]Sheet1’!$A$1
=ADDRESS(1,1,2,1,”[Book1]Sheet1″) Absolute row; relative column to another worksheet in another workbook in A1 style ‘[Book1]Sheet1’!A$1
=ADDRESS(1,1,4,1,”[Book1]Sheet1″) Relative reference to another worksheet in another workbook in A1 style ‘[Book1]Sheet1’!A1

In the upcoming sections, we will see several examples of how we can use this Excel function for different purposes.


7 Examples of Using the Excel ADDRESS Function in Different Cases

To explore the usages of the ADDRESS function, keep going through the following examples. Before we start, let’s see the following datasets that we are going to use in our demonstration.

Sample Datasets to Demonstrate the Usages of the ADDRESS Function in Excel


Example 1: Basic Use of ADDRESS Function in Excel

In the first example, we will learn the basic use of the ADDRESS function.

Simple Cell Address When Only the Row and Column Numbers are Given:

In such cases, the ADDRESS function returns the cell address in A1 reference style (column letter together with the row number), in Absolute Cell Reference by default, and considers the cell in the current worksheet.

📌 Steps

  • First of all, specify the row and column number in Cell B5 and C5 and enter the following formula in Cell D5 to get the corresponding cell address:
    =ADDRESS(B5,C5)

  • After pressing the ENTER key, the function returns the cell address in the format as stated above. The address is $T$12.

Basic Use of ADDRESS Function in Excel

Cell Address with Reference Type, Style, and Sheet Location Given:

Let’s see the same example.

Full Formula:

=ADDRESS(B5,C5,4,0,B8)

📌 Steps

  • Double-click on Cell D5 > Type ADDRESS(B5,C5 > Put a comma and then a space after C5.
    See what happens. 👇

So, if you type 1, you will get the cell address in the absolute reference, if 2,3 or 4 is typed, then you will get absolute row/relative column, relative row/absolute column, and relative reference respectively.

  • Type 4 after the comma, type another comma, and see Excel shows the following options. 👇

If you type 0 (zero), you will get the cell address in R1C1 style. If you type 1, you will get the cell address in A1 style.

  • Type 0 after the comma. Type another comma, and type your sheet name in double quotes like “Example 1”. If you have a list of sheet names, click on the cell that contains the specific sheet name.

Basic Use of ADDRESS Function in Excel

  • Hit ENTER.
    The following picture shows the cell address. Its ‘Example 1’!R[12]C[20].

The ADDRESS function returns the cell address in two reference styles. A1 and R1C1.

Read More: How to Lookup Value from Another Sheet in Excel (3 Easy Methods)


Example 2: Convert Column Number to Letter with Excel ADDRESS Function

We can convert Column Number to Column Letter, and Column Letter to Number using the SUBSTITUTE function with the ADDRESS function. To do that, follow the steps below.

📌 Steps

  • Enter the formula below in Cell C5.
=SUBSTITUTE(ADDRESS(1,B5,4),"1","")

Convert Column Number to Letter with Excel ADDRESS Function

  • Press ENTER and drag the Fill handle icon all the way.
    The following picture shows the Column Letters generated from certain Column Numbers.

Convert Column Number to Letter with Excel ADDRESS Function

🔎 Explanation

  • ADDRESS(1,B5,4)
    The ADDRESS function returns the address in relative reference style.
    Output: “J1”.
  • SUBSTITUTE(ADDRESS(1,B5,4),”1″,””)
    The SUBSTITUTE function substitute 1 with an empty string “”.
    Output: “J”.

In the SUBSTITUTE function, we can keep substituting “1” with “” as 1 is hardcoded in the function.

Read More: How to Lookup Text in Excel (7 Suitable Methods)


Example 3: Addresses of the First and the Last Cells in a Range

Excel ADDRESS function can return the addresses of the first and the last cells in a range of data when combined with other Excel functions like MIN, ROW, MAX, and COLUMN. You wonder, how is that? Let’s see then.

📌 Steps

  • To find the First Cell’s address, enter the following formula in Cell F5:
=ADDRESS(MIN(ROW(B5:D12)),MIN(COLUMN(B5:D12)))

Addresses of the First and the Last Cells in a Range

  • Press ENTER.

  • Enter the following formula for the last cell’s address:
=ADDRESS(MAX(ROW(B8:D15)),MAX(COLUMN(B8:D15)))
  • Press ENTER.
    Look at the following screenshot.

Addresses of the First and the Last Cells in a Range

🔎 How Does the Formula Work?

  • COLUMN(B5:D12)
    Here, the COLUMN function returns the following array.
    Output: {2,3,4}.
  • MIN(COLUMN(B5:D12))
    The MIN function returns the smallest number of the array {2,3,4}.
    Output: {2}.
  • ROW(B5:D12)
    The ROW function returns an array of row-number.
    Output: {5;6;7;8;9;10;11;12}.
  • MIN(ROW(B5:D12))
    Again, the MIN function returns the smallest number in the above array.
    Output: {5}.
    So, the formula ADDRESS(MIN(ROW(B5:D12)),MIN(COLUMN(B5:D12))) now becomes ADDRESS(5,2)
  • ADDRESS(5,2)
    Finally, this returns the address of the first cell in the data range.
    Output: “$B$5”.

The formula to find the address of the last cell works exactly the same way as the above formula does. So, we haven’t discussed it separately.

Read More: 7 Types of Lookup You Can Use in Excel


Example 4: Get Cell Content Using the ADDRESS Function

In this example, we will see an interesting use of the ADDRESS function. When we combine the ADDRESS and INDIRECT functions, we can get the cell content given the row and column number. Let’s see then.

📌 Steps

  • Let’s specify the row and column number first.

  • Type the following formula in Cell F7:
=INDIRECT(ADDRESS(F5,G5))

Get Cell Content Using the Excel ADDRESS Function

  • Press ENTER.

🔎 Explanation:

  • ADDRESS(F5,G5)
    The ADDRESS function returns the cell address given specified the row and column number in Cell F5 and Cell G5.
    Output: “$B$5”.
  • INDIRECT(“$B$5”)
    The INDIRECT function returns the cell content in the specified cell reference, here in Cell B5.
    Output: “S201212001”.

Read More: How to Use LOOKUP Function in Excel (4 Suitable Examples)


Example 5: Address of Cell with the Lowest or the Highest Value

In this example, we will see how to determine the address of the cell having the lowest or the highest value in it.

📌 Steps

  • Type the following formulas in Cell F5 and Cell F7.

For the address of the Lowest Value:

=ADDRESS(MATCH(MIN(D5:D12),D5:D12,0)+4, COLUMN(D5))

For the address of the Highest Value:

=ADDRESS(MATCH(MAX(D5:D12),D5:D12,0)+4, COLUMN(D5))

Address of Cell with the Lowest or the Highest Value

  • Then press ENTER.
    The following screenshot shows the address of the lowest and the highest values.

🔎 How Do the Formulas Work?

  • MIN(D5:D12)
    The MIN function returns the smallest value in the range D5:D12.
    Output: 11476.
  • MATCH(11476,D5:D12,0)+4
    The MATCH function finds an exact match for the output “11476” (as specified by the third argument 0) and returns the relative position.
    As we have placed the dataset in such a way that the first data occurs in the 5th row instead of the 1st row, we have added 4 in the formula to make up for this.
    Output: 2+4.
  • COLUMN(D5)
    The COLUMN function returns the column number of the specified cell reference in its argument.
    Output: {4}.
  • ADDRESS(6,{4})
    Finally, the ADDRESS function returns the cell address with respect to the specified row and column numbers returned by the other functions, MATCH, and COLUMN.
    Output: {“$D$6”}.

Read More: How to Lookup with Multiple Criteria in Excel (Both AND or OR Type)


Example 6: Get Address of a Named Range

To demonstrate this example, let’s first create a named range, “Data” just like the following screenshot. To know how to create a Named Range, click here.

Now, if we want to get the full address of the named range “Data”, we need to follow the steps below.

📌 Steps

  • First of all, type the following formula in Cell F5:
=ADDRESS(@ROW(Data),@COLUMN(Data),4)&":"&ADDRESS(@ROW(Data)+ROWS(Data)-1,@COLUMN(Data)+COLUMNS(Data)-1,4)

Formula to Get Address of Named Range

  • Press ENTER.
    The following picture shows the address of the named range “Data”.

Note:

  • In Excel 2021 and Excel 365, we have to use the Implicit Intersection Operator (@) before the ROW and the COLUMN function in this formula so that the formula doesn’t behave like an array formula and returns a single output.
  • If you use Excel 2019 or an earlier version of Excel, just wipe out the @ sign from the formula and press ENTER.

🔎 How Does the Formula Work?

  • @ROW(Data) and @COLUMN(Data)
    In Excel 365, ROW(Data) would return {5;6;7;8;9;10;11;12} and COLUMN(Data) would return {2,3,4}.
    As we need only the first number in the array, we have added the implicit intersection operator and the output will be the following.
    Output: 5 and 2.
  • ADDRESS(5,2,4)
    The ADDRESS function returns the cell address in relative reference style.
    Output: “B5”.
  • ROWS(Data) and COLUMNS(Data)
    These functions return the number of rows and columns in the Named Range “Data”.
    Output: 8 and 3.
  • ADDRESS(@ROW(Data),@COLUMN(Data),4)
    This part of the formula returns the address of the first cell of the range.
    Output: “B5”.
  • ADDRESS(@ROW(Data)+ROWS(Data)-1,@COLUMN(Data)+COLUMNS(Data)-1,4)
    This part of the formula returns the address of the last cell of the range.
    Output: “D12”.
  • “B5″&”:”&”D12″
    Finally, the ampersands (&) concatenate the two outputs with the colon and return the final output.
    Output: “B5:D12”.

Example 7: Compare Two Columns and Get the Address of the Match

We can also use the ADDRESS function to get the address of the matched values. To do that, we have to combine IF, ISERROR, and MATCH functions with the ADDRESS function. We will use the sample dataset-3 where there are some product data of two shops. We have to find the matches and their cell addresses. Let’s see the following steps.

📌 Steps

  • First, type the following formula in Cell E5 and press ENTER.
=IF(ISERROR(MATCH(B5,$C$5:$C$12,0)),"Unique",ADDRESS(MATCH(B5,$C$5:$C$12,0),2))

  • Then pull the Fill handle icon all the way.

Compare Two Columns and Get the Address of the Match

🔎 How Does the Formula Work?

  • MATCH(B5, $C$5:$C$12,0)
    The MATCH function finds the match in the range C5:C12 for the content in Cell B5 and returns the relative position of the exact match in C5:C12.
    Output: 3.
  • ADDRESS(3,2)
    The ADDRESS function returns the cell address of the cell where the 3rd row and the 2nd column intersect. We can put the column number as 2 as it is hardcoded in the formula.
    Output: “$B$3”.
  • IF(ISERROR(MATCH(B5, $C$5:$C$12,0)),”Unique”, MATCH(B5, $C$5:$C$12,0),2)
    If the MATCH function returns #N/A, then it is tested by the ISERROR function, which returns TRUE or FALSE. The IF function returns “Unique” if the statement is TRUE and if FALSE, returns the Cell Address.
    Output: $B$3, Unique, Unique, Unique, $B$8, $B$6, $B$7, Unique.

Read More: How to Lookup Multiple Values in Excel (10 Ways)


💡 Things to Keep in Mind

  • Please be careful and write valid arguments in the ADDRESS function.
  • Invalid arguments like row or column number less than 1 or greater than the number facilitated by Excel, any non-numeric input for row_num, column_num, or [abs_num] arguments will return #VALUE! error.
  • [a1] logical input should be one of the recognized inputs provided by Excel.

Conclusion

So, we have discussed 7 uses of the Excel ADDRESS function. If you have any confusion or question, please feel free to ask us in the comment box. Also, let us know if you find the article useful. Please visit ExcelDemy.com and read more articles based on various Excel topics. Happy reading!


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo