Go Back

Excel ADDRESS Function

You can use the Excel ADDRESS function to get the address for cells in a worksheet, based on particular rows and columns. This function returns an address either in a relative or absolute format. This article provides a clear guide on how to use the Excel ADDRESS function.

Figure 1. Final result

Syntax of the formula

=ADDRESS (row_num, col_num, [abs_num], [a1], [sheet])

Where;

  • Row_num- refers the row number we want to use in the cell address
  • Col_num- refers to the column number we want to use the cell address
  • Abs_num– address type. The default of the address type is absolute. This is optional and can be ignored.
  • A1- refers to the reference style, which is also optional. Default style is A1.
  • Sheet– identifies the name of the worksheet we want to use. Default is the current sheet, and it is optional as well.

Understanding the ADDRESS function

This function is fundamental when we want to get the address of a given cell based on a given row and column. The formula returns a cell address in from the given worksheet. This function returns the cell address in either absolute format or relative format.

Example 1

To further understand how the Excel ADDRESS function works, we shall look at an example where we shall fetch the address from a specified row and column.

Figure 2. Example 1

In this example, the ‘result’ column shows the address for the specified rows and columns in the worksheet.

Note

There are four keys that can help us define the Abs_num, these are;

  • Omitted absolute
  • Relative row; absolute column
  • Absolute row; relative column
  • Relative

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hello, I need a formula to help merge data. I have a spreadsheet of buildings and addresses. We have a data set of shipping addresses and billing addresses. Some have shipping address filled out with billing blank and some have billing address filled out with shipping address blank. I would like to combine these fields. Example: Building A has shipping address and no billing address Building B has billing address and no shipping address I would l like a formula to easily move Building B's billing address into the shipping address field so I can delete billing address and just have one column for "address"
Solved by K. H. in 12 mins
The answer I got earlier: =CONCATENATE(INDIRECT(CELL("address",$A2)),INDIRECT(CELL("address",$B2)),INDIRECT(CELL("address",$C2))) Basically doesn't work. :(
Solved by E. W. in 11 mins
hi, I want to match two sets of address and pull out a unique ref code if the address match. Thanks
Solved by I. J. in 17 mins

Leave a Comment

avatar