How to use the ADDRESS function
The ADDRESS function returns the address of a specific cell, you need to provide a row and column number.
Table of Contents
1. Excel Function Syntax
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
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. |
3. ADDRESS Function example
Formula in cell D3:
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.
4. 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.
The image above shows a #VALUE! error, the second argument expects a cell reference or a number, however, a text string is used.
5. ADDRESS function - return value
Formula in cell D3:
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".
6. 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:
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".
7. ADDRESS function another sheet
The image above demonstrates a formula in cell D3 that returns a value from another sheet using the ADDRESS function.
Formula in cell D3:
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.
8. Create an address to a 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:
8.1 Explaining formula
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.
9. Extract column letter
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:
Explaining formula
Step 1 - Get column number
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))
'ADDRESS' Function examples
The following 4 articles have formulas containing the ADDRESS function.
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]
This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]
Functions in 'Lookup and reference' category
The ADDRESS function function is one of many functions in the 'Lookup and reference' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form