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. Combine with INDIRECT function, to get a specific cell's value. Or, combine with COLUMN function, to get the column letter for any cell. 
ADDRESS SyntaxThe ADDRESS function is one of the Excel Lookup and Reference functions, and it has the following syntax:
Here are the ADDRESS arguments  the first 2 are required arguments in ADDRESS formulas, and the last one is an optional argument:

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 name of the worksheet, "Ex02", is hardcoded 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 suggesting this formula. The formula examples below show how to do this for:
1) Column Letter for Specific Column NumberTo return the column letter for a specific column number, follow the steps below:
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. 
2) Column Letter for Formula CellTo make a flexible column letter formula, you can use the COLUMN function, inside the ADDRESS function. To return the column letter for the cell that contains the ADDRESS formula, follow the steps below:
In the screen shot below, the formula was entered in cell B4, and the copied across to cell E4. Note: This formula also returns the correct result for 2letter columns and 3letter columns. How It WorksIn this example, the COLUMN function is used without a cell reference. Because the cell reference argument is omitted, the COLUMN function returns the column number of the current cell. 
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 26, 2023 2:23 PM