Excel ADDRESS function examples show how to get a cell address as text, based on a row and column number.
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.
To see how the ADDRESS function works, watch this short video tutorial. The video uses the sample workbook from this tutorial.
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 other formulas shown in Example 2, below.
With the ADDRESS function, you can get a cell address, as text, based on a row number and column number.
If you enter just the row and column arguments, the result is an absolute address, in A1 style. In the screen shot below, this formula is entered in cell C4:
The result is $C$1
There are 4 options for the abs_num argument:
If you use 1, or omit the abs_num argument, the result is an absolute reference.
In the following formula, there is a 4 entered in the abs_num argument:
Now, the result is shown as a relative reference -- C1
The 4th argument for the ADDRESS function, a1, controls the reference style - A1 or R1C.
In the following formula, FALSE is the 4th argument:
The result, in cell C4, is an absolute reference (1), shown in R1C1 style -- "R1C3"
The 5th argument for the ADDRESS function is sheet_text. You can type a sheet name in the formula, or refer to a cell that contains a sheet name.
In this formula, the sheet name, "Ex02", is hard-coded into the formula
In the next formula, a sheet name is selected from a drop down list in cell C4, and the formula refers to that cell.
In this example, the goal is to get the value from a specifi cell, based on its row and column number. We'll need two functions to achieve this goal.
Here is the formula in cell C4, which returns the value from the specified address.
In this example, we'll find the cell with the highest value, and use the ADDRESS function to get its address.
First, in cell F3, the MAX function finds the highest number in column C.
Next, in cell F4, the ADDRESS function is combined with two other functions:
Here is the formula from cell F4, with all three functions:
In this example, we'll find a column letter, based on a column number, such as "D" for column 4. Thanks to Luke Wisbey for this formula.
Here's how the ADDRESS section of the formula works:
The result of the ADDRESS section, in this example, is CV1
Next, the SUBSTITUTE function replaces the "1" in the address with an empty string.
The result of the formula is CV -- the column letter(s), without the row number.
For a variation on this formula, you could use the COLUMN function, without a cell reference, instead of referring to a cell with a column number.
This formula will give the column letter for whatever column the formula is in.
To see the formulas used in these examples, download the ADDRESS function sample workbook. The file is zipped, and is in Excel xlsx file format. There are no macros in the workbook
Last updated: November 9, 2018 11:08 AM
Contextures RSS Feed