Home > Formulas > Text > Address ADDRESS Function ExamplesExcel ADDRESS function examples show how to get a cell address as text, based on a row and column number. |
ADDRESS TrapsThe 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. Ex 1: Cell address from row and columnWith 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: =ADDRESS($C$2,$C$3) The result is $C$1
|
Absolute or Relative - abs_num argumentThere 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: =ADDRESS($C$2,$C$3,4) Now, the result is shown as a relative reference -- C1
Reference Style - a1 argumentThe 4th argument for the ADDRESS function, a1, controls the reference style - A1 or R1C.
In the following formula, FALSE is the 4th argument: =ADDRESS($C$2,$C$3,1,FALSE) The result, in cell C4, is an absolute reference (1), shown in R1C1 style -- "R1C3"
|
Sheet Name - sheet_textThe 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 =ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
In the next formula, a sheet name is selected from a drop down list in cell C4, and the formula refers to that cell. =ADDRESS($C$2,$C$3,1,TRUE,C4) Ex 2: Cell value from row and column numberIn 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. =INDIRECT(ADDRESS(C2,C3))
|
Ex 3: Address of cell with highest valueIn 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. =MAX(C3:C8)
Next, in cell F4, the ADDRESS function is combined with two other functions:
Here is the formula from cell F4, with all three functions: =ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
|
Ex 4: Column Letter from Column NumberIn 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.
How It WorksHere'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. Formula VariationFor 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. =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") |
Download the Sample FileTo 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 More Tutorials |
Last updated: December 29, 2022 7:02 PM