How to use the ADDRESS function

Author: Oscar Cronquist Article last updated on September 29, 2022

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

ADDRESS Function Syntax
ADDRESS Function Arguments
ADDRESS Function example
ADDRESS function not working
Get value based on row and column number
Get value based on row and column number - ADDRESS function alternative
Get value based on a row and column number and a worksheet name
Create an address to a cell range and return values from that cell range
Extract column letter - Excel 365
Get Excel *.xlsx file

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:

=ADDRESS(B3, C3)

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

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.

5. Get value based on row and column number

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".

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. Get value based on row and column number - 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)

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. Get value based on a row and column number and a worksheet name

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.

8. Create an address to a cell range and return values from that 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.

Array 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.

9. Extract column letter - Excel 365

ADDRESS Function get column letter 1

The image above 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))

'ADDRESS' function examples

The following 4 articles contain the ADDRESS function.

Convert column number to column letter
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]

Easily select data using hyperlinks
The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]

How to quickly find the maximum or minimum value [Formula]
This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]

Locate lookup values in an Excel table [HYPERLINK]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]

Functions in this article

ADDRESS INDIRECT INDEX LET COLUMN LEFT LEN

Functions in 'Lookup and reference' category

The ADDRESS function function is one of many functions in the 'Lookup and reference' category.

ADDRESS function

Returns the address of a specific cell, you need to provide a row and column number.

AREAS function

Returns the number of cell ranges and single cells in a reference.

CHOOSE function

Gets a value based on a number.

COLUMN function

Returns the column number of the top-left cell of a cell reference.

COLUMNS function

Calculates the number of columns in a cell range.

FILTER function

Extracts values/rows based on a condition or criteria.

FORMULATEXT function

Returns a formula as a text string.

HLOOKUP function

Searches the top row in a data range for a value and return another value on the same column in a row you specify.

HYPERLINK function

Builds a link in a cell.

INDEX function

Returns a value or reference from a cell range or array, you specify which value based on a row and column number.

INDIRECT function

Returns the cell reference based on a text string and shows the content of that cell reference.

LOOKUP function

Find a value in a cell range and return a corresponding value on the same row.

MATCH function

Returns the relative position of an item in an array that matches a specified value in a specific order.

OFFSET function

Returns a reference to a range that is a given number of rows and columns from a given reference.

ROW function

Calculates the row number of a cell reference.

ROWS function

Calculate the number of rows in a cell range.

SORT function

Sorts values from a cell range or array

SORTBY function

Sorts a cell range or array based on values in a corresponding range or array.

STOCKHISTORY function

Downloads stock prices based on a stock quote

TRANSPOSE function

Converts a vertical range to a horizontal range, or vice versa.

UNIQUE function

Returns a unique or unique distinct list.

VLOOKUP function

Lets you search the leftmost column for a value and return another value on the same row in a column you specify.

XLOOKUP function

Search one column for a given value, and return a corresponding value in another column from the same row.

XMATCH function

Searches for an item in an array or cell range and returns the relative position.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

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

How to use the ADDRESS function

Table of Contents

1. Excel Function Syntax

2. Arguments

3. ADDRESS Function example

3.1 Explaining formula

Step 1 - ADDRESS Function

Step 2 - Populate arguments

Step 3 - Evaluate ADDRESS function

4. ADDRESS function not working

5. Get value based on row and column number

5.1 Explaining formula

Step 1 - Calculate address

Step 2 - Convert string to cell reference

6. Get value based on row and column number - ADDRESS function alternative

6.1 Explaining formula

Step 1 - INDEX function

Step 2 - Populate arguments

Step 3 - Evaluate INDEx function

7. Get value based on a row and column number and a worksheet name

7.1 Explaining formula

Step 1 - ADDRESS function

Step 2 - Populate arguments

Step 3 - Create cell reference

8. Create an address to a cell range and return values from that cell range

8.1 Explaining formula

Step 1 - Top left cell

Step 2 - Bottom right cell

Step 3 - Create a cell address to a cell range

Step 4 - Create a cell reference

9. Extract column letter - Excel 365

Explaining formula

Step 1 - Get column number

Step 2 - Create cell address

Step 3 - Count characters

Step 4 - Remove row number

Step 5 - Shorten formula

'ADDRESS' function examples

Functions in this article

Functions in 'Lookup and reference' category

Excel function categories

Excel categories

Leave a Reply

How to comment