Author: Oscar Cronquist Article last updated on March 08, 2022

The ADDRESS function returns the address of a specific cell, you need to provide a row and column number.

1. Excel Function Syntax

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Back to top

2. Arguments

row_num Required. A number representing the row number.
column_num Required. A number representing the column number.
[abs_num] Optional. Lets you choose the type of reference to return.
1 - Absolute.
2 - Absolute row, relative column.
3 - Absolute column, relative row.
4 - Relative.
[a1] Optional. Lets you choose reference style. A1 reference style is the default setting you are probably used to. The R1C1 reference style has both columns and rows are labeled numerically.
TRUE - A1-style reference.
FALSE - R1C1-style reference.
[sheet_text] Optional. A text value that specifies the name of the worksheet to be used as the external reference. =ADDRESS(1,1,,,"Sheet2") returns Sheet2!$A$1. If omitted the function refers to a cell on the current sheet.

Back to top

3. ADDRESS Function example

Formula in cell D3:

=ADDRESS(B3, C3)

Back to top

3.1 Explaining formula

Step 1 - ADDRESS Function

The ADDRESS function calculates the address of a specific cell based on a row and column number.

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Step 2 - Populate arguments

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

row_num - B3

column_num - C3

[abs_num], - optional

[a1] - optional

[sheet_text]) - optional

Step 3 - Evaluate ADDRESS function

ADDRESS(1, 30)

and returns "$AD$1". This is a string, not a cell reference.

Back to top

4. ADDRESS function not working

ADDRESS Function not working

Make sure your spelling is correct, the image above shows a #NAME! error in cell D3. The ADDRESS function is misspelled.

ADDRESS Function not working1

The image above shows a #VALUE! error, the second argument expects a cell reference or a number, however, a text string is used.

Back to top

5. ADDRESS function - return value

ADDRESS Function return value

Formula in cell D3:

=INDIRECT(ADDRESS(B3, C3))

5.1 Explaining formula

Step 1 - Calculate address

ADDRESS(B3, C3)

becomes

ADDRESS(7, 3)

and returns C7.

Step 2 - Convert string to cell reference

The INDIRECT function creates a cell reference from on a value.

INDIRECT(ref_text, [a1])

INDIRECT(ADDRESS(B3, C3))

becomes

INDIRECT("C7")

and returns "C".

Back to top

The INDIRECT function is a volatile function meaning it will recalculate more often than non-volatile functions. Extensive use of the INDIRECT function or other volatile functions may slow down your workbook considerably.

6. ADDRESS function alternative

ADDRESS Function alternative

I recommend the INDEX function instead of the ADDRESS function to create a cell reference that you will be using to get a value.

Formula in cell D3:

=INDEX(A1:E11,B3,C3)

Back to top

6.1 Explaining formula

Step 1 - INDEX function

The INDEX function returns

INDEX(reference, [row_num], [column_num], [area_num])

reference -
row_num -
column_num -
area_num - 

Step 2 - Populate arguments

INDEX(reference, [row_num], [column_num], [area_num])

reference - A1:E11
row_num - B3
column_num - C3
area_num - optional

Step 3 - Evaluate INDEx function

INDEX(A1:E11,B3,C3)

becomes

INDEX(A1:E11,7,3)

becomes cell reference C7

and returns "C".

Back to top

7. ADDRESS function another sheet

ADDRESS Function another worksheet

The image above demonstrates a formula in cell D3 that returns a value from another sheet using the ADDRESS function.

Formula in cell D3:

=INDIRECT(ADDRESS(B3, C3, , , "ADDRESS function"))

7.1 Explaining formula

Step 1 - ADDRESS function

The ADDRESS function calculates the address of a specific cell based on a row and column number.

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Step 2 - Populate arguments

row_num - B3

column_num - C3

[abs_num], - optional

[a1] - optional

[sheet_text]) - "ADDRESS function"

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

becomes

ADDRESS(B3, C3, , , "ADDRESS function")

and returns 'ADDRESS function'!$B$3.

Step 3 - Create cell reference

The INDIRECT function creates a cell reference from on a value.

INDIRECT(ref_text, [a1])

INDIRECT(ADDRESS(B3, C3, , , "ADDRESS function"))

becomes

INDIRECT('ADDRESS function'!$B$3)

and returns 1.

Back to top

8. Create an address to a cell range

ADDRESS Function cell range

The image above shows a formula in cell E3 that creates a cell reference to a cell range using the ADDRESS function.

Formula in cell E3:

=INDIRECT(ADDRESS(B3,C3)&":"&ADDRESS(B4,C4))

8.1 Explaining formula

ADDRESS Function cell range1

Step 1 - Top left cell

ADDRESS(B3,C3)

becomes

ADDRESS(8,2)

and returns string "B8".

Step 2 - Bottom right cell

ADDRESS(B4,C4)

becomes

ADDRESS(9,3)

and returns string "C9".

Step 3 - Create a cell address to a cell range

The ampersand character lets you concatenate text strings in an Excel formula.

ADDRESS(B3,C3)&":"&ADDRESS(B4,C4)

becomes

B8&":"&C9

and returns the string "B8:C9".

Step 4 - Create a cell reference

The INDIRECT function creates a cell reference from on a value.

INDIRECT(ref_text, [a1])

INDIRECT(ADDRESS(B3,C3)&":"&ADDRESS(B4,C4))

becomes

INDIRECT("B8:C9")

and returns cell reference B8:C9.

Back to top

9. Extract column letter

ADDRESS Function get column letter 1

The image abover shows a formula in cell B3 that extracts the column letters from a given cell reference, in this example BC465.

Excel 365 formula in cell B3:

=LET(x, ADDRESS(1, COLUMN(BC465), 4), LEFT(x, LEN(x)-1))

Explaining formula

Step 1 - Get column number

ADDRESS Function get column letter count columns

The COLUMN function returns a number representing the column of a given cell reference counting from the left.

COLUMN(cell_ref)

COLUMN(BC465)

returns 55. Column BC465 is the 55th column counting from the left, see the image above.

Step 2 - Create cell address

ADDRESS(1,COLUMN(BC465),4)

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

[abs_num] - Optional. Let's you choose the type of reference to return.
1 - Absolute.
2 - Absolute row, relative column.
3 - Absolute column, relative row.
4 - Relative.

ADDRESS(1,COLUMN(BC465),4)

becomes

ADDRESS(1,55,4)

and returns BC1.

Step 3 - Count characters

The LEN function returns a number representing the total amount of characters in a string.

LEN(str)

LEN(ADDRESS(1,COLUMN(BC465),4))-1

becomes

LEN("BC1")-1

becomes

3 - 1 equals 2.

Step 4 - Remove row number

The LEFT function extracts a specific number of characters always starting from the left.

LEFT(ADDRESS(1,COLUMN(BC465),4),LEN(ADDRESS(1,COLUMN(BC465),4))-1)

becomes

LEFT("BC1",2)

and returns "BC".

Step 5 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LEFT(ADDRESS(1,COLUMN(BC465),4),LEN(ADDRESS(1,COLUMN(BC465),4))-1)

ADDRESS(1, COLUMN(BC465), 4) is repeated twice in the formula.

x - ADDRESS(1, COLUMN(BC465), 4)

LET(x, ADDRESS(1, COLUMN(BC465), 4), LEFT(x, LEN(x)-1))

Back to top