Yesterday, 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.
The ADDRESS function 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.
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
The ADDRESS function has the following syntax:
- 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
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.
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.
A1 or R1C
To see R1C1 style, instead of the default A1 style, you can add FALSE in the a1 argument.
The final argument is sheet, and you can include a sheet name, if you want it in the result.
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.
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)
The INDEX function can also return a cell's value, based on a row and column number.
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.
Then, the ADDRESS function is combined with MATCH, which finds the row number, and COLUMN, which gets the column number.
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, watch this short video.