How to Use the ADDRESS Function in Excel

This guide will explain how to use the ADDRESS function in Excel.

Referencing cells manually can be time-consuming and prone to errors due to manual input. To create dynamic cell references more efficiently and reduce the risk of error, it is advisable to use functions. 

The ADDRESS function allows users to return a specific cell address given a row number and column number input. The ADDRESS function is commonly used alongside other Excel functions that require cell reference input such as INDIRECT, MATCH, and INDEX.

In this guide, we will provide a step-by-step tutorial on how to use the ADDRESS function to create a cell reference dynamically in Microsoft Excel.

The Anatomy of the ADDRESS Function

The syntax of the ADDRESS function is as follows:

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

Let’s look at each argument to understand how to use the ADDRESS function.

• = the equal sign is how we start any function in Excel.
• ADDRESS() refers to the ADDRESS function in Excel. The function returns the address of a cell given a specified row and column number.
• row_num must be a numeric value that specifies the row number of the cell reference you wish to use.
• column_num should be a numeric value that specifies the column number of your desired cell reference.
• The abs_num argument is an optional input that specifies the type of reference to return.
  • If set to 1 or omitted, the function returns an absolute reference.
  • If set to 2, the output will have an absolute row and relative reference.
  • If set to 3, the output will have a relative row and absolute column.
  • If the argument is set to 4, both the row and column will be a relative reference.
• The A1 argument specifies whether the output will follow the A1 or R1C1 reference style. By default, the ADDRESS function returns A1-style references.
• The sheet_text function may be necessary if you wish to generate an external reference. If no sheet_text argument is used, the output of ADDRESS will refer to a cell in the current sheet.

A Real Example of Using the ADDRESS Function in Excel

Let’s explore a simple example where we can use the ADDRESS function to create a cell reference.

In the table above, we have a dataset of scores from an exam. We want to output the cell address of the highest score in the dataset.

To do this, we can use the following formula:

=ADDRESS(MATCH(MAX(A1:A11),A1:A11,0),COLUMN(A1))

Since we already know the column of our output (column A), we can use COLUMN(A1) to return the appropriate column number. Next, we’ll use the MAX and MATCH functions together to return the position of the highest value in the range A1:A11. 

In this particular example, MATCH(MAX(A1:A11) will return a value of 6. This value will be used as the row number in our ADDRESS function.

In our spreadsheet above, the ADDRESS function returns the cell address $A$6 which indeed corresponds to the cell with the largest value.

To return the actual value of the cell reference, we can use the INDIRECT function. The INDIRECT function allows us to return the reference specified by a text string.

Using the formula MATCH(MAX(A1:A11), we can output the actual maximum value of our range (99) in a new cell.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the ADDRESS function in Excel.

How to Use the ADDRESS Function in Excel

1. First, select a blank cell where you wish to return a cell reference using the ADDRESS function.
   
   In this example, we’ll return a cell reference in cell B1.
   
2. Type “=ADDRESS(“ to start the ADDRESS function.
   
   Next, enter the row and column to use for the cell reference.
   
3. Hit the Enter key to evaluate the function.
   
   In our example above, we used the ADDRESS function =ADDRESS(2,6) to create the cell reference $F$2.
   
4. We can adjust the third argument abs_num to switch between absolute reference, relative reference, or a mix of both.
   
5. We can set the fourth argument to 0 to output an R1C1-style cell reference instead.
   
6. The optional fifth argument allows us to specify a sheet name for the cell reference output.
   
   In the example above, we included the sheet name “Sheet2” as part of our formula to return “Sheet2!$F$2”.

That’s all the steps you need to know to start using the ADDRESS function in Excel.

FAQs

1. How do I use the ADDRESS function to create a cell reference with a different reference style?
   To use a different reference style (R1C1 or A1), we can modify the optional [a1] argument of the ADDRESS function. Set this argument to TRUE for the A1 style and FALSE for the R1C1 style.
2. Can I use the ADDRESS function to reference cells in a different worksheet?
   Yes, it is possible to reference cells in different worksheets using the ADDRESS function. You must use the sheet_text argument of the ADDRESS function to specify the sheet name where the cell is located.

To learn more about the ADDRESS function, you can read our post on how to get the cell address instead of value in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!