# 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 signsUse 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 OscarYou can contact me through this contact form