The ADDRESS Function In Excel: How To Use It

Have you ever needed to get the address of a cell in Excel? Maybe you need to reference it in a formula or copy it to another location. The ADDRESS function can help you do this. It returns the text string for the specified row and column coordinates. In this blog post, we will show you how to use the ADDRESS function in Excel, and provide some examples of how it can be helpful.

What Does ADDRESS Do?

The ADDRESS function is designed to return a cell reference in text format based on the row and column numbers you supply it with. Even better, you can return ranges in addition to single cells.

By itself, ADDRESS isn’t all that powerful as any Excel user can easily point to another cell. The real power comes in combination with formulas like INDIRECT and MATCH when you don’t know which cell you will want to target.

Where To Find It?

Option 1:

The ADDRESS function can be found in the Formulas tab of Excel under Lookup & Reference

Option 2:

You can use Formula Builder to walk you through the formula step by step.

Option 3:

You can manually type the formula into any cell.

The Formula

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

• row_num – the row number of the cell you want the address from
• coumn_num – the column number of the cell you want the address from
• abs_num (optional) – the reference type, whether you want absolute or relative (think $)
  • 1 (default) = absolute such as $1A$1
  • 2 = mixed such as A$1
  • 3 = mixed such as $A1
  • 4 = relative such as A1
• a1 (optional) – the style of the reference
  • 1 (default) =  a normal reference such as A1
  • 0 = a reference in R1C1 format where A1 would be R[1]C[1]
• sheet_text (optional) – the name of the worksheet to include in the reference which must be included as a text string in “”. If left blank, it defaults to the current sheet

Let’s Take a Look at Some Real-World Examples

Excel Workbook

Video Walkthrough

Example 1 – Beginner

In the first example, we will work with the different arguments in the ADDRESS Function to call cell references. As shown below, I have done one example to highlight each one of the arguments.

The first example shows you how to work with the row and column arguments. The second, shows how to work with absolute, relative, and mixed references. The third, shows how to work with the R[1]C[1] format. Lastly, the fourth shows how to reference another sheet.

Your turn: Use the workbook and the ADDRESS function to follow the examples in blue and finish the practice formulas highlighted in green

Example 2 – Intermediate

For the second example, we are going to use ADDRESS with INDIRECT to return the value in a cell. INDIRECT will provide a value if given a cell reference, exactly what ADDRESS does. In the first example, we demonstrate that ADDRESS will return $G$10 if used by itself to reference South sales.

If we nest the same formula inside INDIRECT, it returns $5,000 which is the correct value for South sales.

Your turn: Use the workbook and the ADDRESS function to follow the examples in blue and finish the practice formulas highlighted in green

Example 3 – Advanced

In the final example, we will use MATCH inside of ADDRESS to fill in some of our arguments. Keep in mind, you can use this trick to pull in many different formulas, or even just reference cells containing column and row values.

In the first example, we will use MATCH to replace the row value. We want MATCH to count down to South, keeping in mind that ADDRESS can only start at the first row. We then manually enter column 7 since it is fixed.

For the second example, we use Match to find the cell with the lowest sales volume. We set the match equal to the minimum value (calculated using MIN Function) in G12. ADDRESS then looks for the low value and returns cell $G$8 which is the correct lowest sales volume.

Your turn: Use the workbook and the ADDRESS function to follow the examples in blue and finish the practice formulas highlighted in green

Tips and Tricks

Better Together

ADDRESS works in tandem with any other function that expects to receive a reference. It pairs especially well with INDIRECT, SUBSTITUTE, and MATCH Functions.

If You Get a #REF Error

This usually means the address returned by the function is invalid. Most often, your address went off the worksheet. Rethink your column and row references to stay within bounds.

If You Get a #VALUE! Error

One of your arguments was not formatted correctly. This is the correct format for each argument:

• row_num – the row number of the cell you want the address from
• coumn_num – the column number of the cell you want the address from
• abs_num (optional) – the reference type, whether you want absolute or relative (think $). The default is an absolute reference. 1=absolute $A$1, 2= mixed A$1, 3=mixed $A1, 4= relative A1
• a1 (optional) – the style of the reference. The default or 1 = a normal reference such as A1. 0 = a numerical reference such as 11 (column 1, row 1)
• sheet_text (optional) – the name of the worksheet to include in the reference which must be included as a text string in “”. If left blank, it defaults to the current sheet.

Let’s Recap

In this blog post, we have shown you how to use the ADDRESS function in Excel. We have provided some real-world examples of how ADDRESS can be used in your workbooks. We also shared some tips and tricks for using ADDRESS with other functions and preventing errors. Be on the lookout for more articles on Excel and financial wizardry.

Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.