30 Excel Functions in 30 Days: 20 – ADDRESS

Icon30DayYesterday, in the 30XL30D challenge, we found items in an array with the MATCH function, and learned that it plays nicely with other functions, like VLOOKUP and INDEX.
For day 20 in the challenge, we’ll examine the ADDRESS function. It returns a cell address as text, based on a row and column number. Do you need ADDRESS? Do other functions do the same thing, but better?
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the ADDRESS information and examples, and if you have other tips or examples, please share them in the comments.

Function 20: ADDRESS

The ADDRESS function returns a cell address as text, based on a row and column number. It can return an absolute or relative address, in A1 or R1C1 style. A sheet name can also be included in the result.
Address00

How Could You Use ADDRESS?

The ADDRESS function can return a cell address, or combine with other functions to:

  • Get cell address from row and column number
  • Find cell value from row and column number
  • Return address of cell with highest value

ADDRESS Syntax

The ADDRESS function has the following syntax:

  • ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
    • abs_num — if 1 or omitted, Absolute ($A$1), for Relative (A1), use 4. Other options, 2=A$1, 3=$A1
    • a1 — if TRUE or omitted, returns A1 style, FALSE for R1C1 style
    • sheet_text — sheet name can be included, for address on different sheet

ADDRESS Traps

The ADDRESS function only returns the cell address as text. If you need the cell value, use the INDIRECT function around the ADDRESS function, or use one of the alternative formulas shown in Example 2.

Example 1: Get cell address from row and column number

With the ADDRESS function, you can get a cell address, as text, based on a row number and column number. If you enter just those two arguments, the result is an absolute address, in A1 style.
=ADDRESS($C$2,$C$3)
Address01a

Absolute or Relative

By omitting the abs_num argument in the formula above, the result was the default, absolute reference.
To see the address as a relative reference, you can use 4 in the abs_num argument.
=ADDRESS($C$2,$C$3,4)
Address01b

A1 or R1C

To see R1C1 style, instead of the default A1 style, you can add FALSE in the a1 argument.
=ADDRESS($C$2,$C$3,1,FALSE)
Address01c

Sheet Nam

The final argument is sheet, and you can include a sheet name, if you want it in the result.
=ADDRESS($C$2,$C$3,1,TRUE,”Ex02?)
Address01d

Example 2: Find cell value from row and column number

The ADDRESS function returns the cell address as text, not as an actual reference. If you want to return the cell’s value, you can wrap the ADDRESS function with an INDIRECT function. We’ll learn more about INDIRECT, later in the 30XL30D challenge.
=INDIRECT(ADDRESS(C2,C3))
Address02a
The INDIRECT function will work without the ADDRESS function too. Here, the & operator is used to create an R1C1 style address, and the cell value is returned.
=INDIRECT(“R” & C2 & “C” & C3,FALSE)
Address02b
The INDEX function can also return a cell’s value, based on a row and column number.
=INDEX(1:500,C2,C3)
Address02c

Example 3: Return address of cell with highest value

In this example, we’ll find the cell with the highest value, and use the ADDRESS function to get its address.
The MAX function finds the highest number in column C.
=MAX(C3:C8)
Address03a
Then, the ADDRESS function is combined with MATCH, which finds the row number, and COLUMN, which gets the column number.
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
Address03b

Download the ADDRESS Function File

To see the formulas used in today’s examples, you can download the ADDRESS function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the ADDRESS Video

To see a demonstration of the examples in the ADDRESS function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Get Cell Address as With Excel ADDRESS Function

_____________