ADDRESS Function

Returns a cell reference, as text, from a row and column number.

Category

Lookup & Reference

Available in

Excel 2007 and later, Microsoft 365

Returns

Text, e.g. "$C$5"

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Reference Functions module

Overview

The Excel ADDRESS function turns a row number and a column number into a cell address such as $C$5. Think of it as the reverse of looking at a cell: instead of seeing "C5" and knowing it's row 5, column 3, you give Excel 5 and 3 and it tells you "C5".

The important thing to understand is that ADDRESS returns text, not an actual reference. It tells you where something is, but it doesn't fetch the value in that cell. To do that, you pair it with INDIRECT — we'll cover that below.

Syntax

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

Arguments

ArgumentRequired?What it does
row_numYesThe row number of the cell (1 = row 1).
column_numYesThe column number of the cell (1 = column A, 3 = column C, 28 = column AB).
abs_numOptionalWhich parts are locked with $. Defaults to 1 (fully absolute). See the table below.
a1OptionalTRUE (default) for A1 style like $C$5. FALSE for R1C1 style like R5C3.
sheet_textOptionalA sheet name to put in front of the address, e.g. "Sales" gives Sales!$C$5.

Reference styles (abs_num)

The third argument controls where the dollar signs go. Here's every option for row 5, column 3:

ABC
1abs_numFormulaResult
21 (default)=ADDRESS(5,3)$C$5
32=ADDRESS(5,3,2)C$5
43=ADDRESS(5,3,3)$C5
54=ADDRESS(5,3,4)C5
61, R1C1 style=ADDRESS(5,3,1,FALSE)R5C3
Each result in column C is plain text produced by the formula in column B.
Not sure which to pick? Use 4 (relative, no $ signs) when you just want a readable label, and leave it at the default 1 when you'll feed the result into INDIRECT.

Examples

Example 1 — The basics

Let's start simple. We have row and column numbers in columns B and C, and we want the address in column D.

D5fx=ADDRESS(B5, C5)
ABCD
4RowColumnAddress
511$A$1
653$C$5
71028$AB$10

The formula in D5 (copied down) is:

=ADDRESS(B5, C5) Explain

Breaking it down:

=ADDRESS(B5// B5 is row_num → 1
=ADDRESS(B5, C5)// C5 is column_num → 1, so column A

Notice in row 7 that column 28 becomes AB — ADDRESS handles the letter conversion for you.

Example 2 — Adding a sheet name

When the reference needs to point at another sheet, pass the name as the fifth argument. If the sheet name contains spaces, Excel adds the single quotes automatically.

=ADDRESS(2, 4, 1, TRUE, "Q1 Sales") Explain

Result: 'Q1 Sales'!$D$2

Example 3 — Finding where a value lives (with MATCH)

Here's where ADDRESS earns its keep. Say we want to know which cell holds the highest sales figure. MATCH finds the position, and ADDRESS turns it into an address.

E2fx=ADDRESS(MATCH(MAX(B2:B6),B2:B6,0)+1, 2)
ABCDE
1RepSalesTop sale is in
2Ana4,200$B$4
3Ben3,150
4Cara6,800
5Dev5,020
6Eli2,990
=ADDRESS(MATCH(MAX(B2:B6), B2:B6, 0) + 1, 2) Explain
  1. MAX(B2:B6) finds the highest sale: 6,800.
  2. MATCH(…, B2:B6, 0) finds its position in the list: 3rd.
  3. We add 1 because the list starts in row 2, not row 1 — so the real row is 4.
  4. ADDRESS(4, 2) returns $B$4.
Forgetting the "+1" offset is the most common mistake here. MATCH returns a position within your range, while ADDRESS needs the row number on the sheet.

Example 4 — Getting the value back (with INDIRECT)

Because ADDRESS only returns text, wrap it in INDIRECT to read the value that lives at that address:

=INDIRECT(ADDRESS(4, 2)) Explain

Using the table above, this returns 6,800. This combo is handy when the row or column you need is calculated elsewhere in your workbook.

INDIRECT recalculates every time anything in the workbook changes, which can slow large files. If you only need the value, INDEX(B:B, 4) or XLOOKUP is usually faster.

Common mistakes

  • Expecting a value, getting text. ADDRESS gives "$B$4", not what's in B4. Use INDIRECT if you need the value.
  • #VALUE! errors. row_num or column_num is below 1, above Excel's limits (1,048,576 rows / 16,384 columns), or isn't a number.
  • Off-by-one rows. When combining with MATCH, add the number of rows above your range.

Notes

  • ADDRESS always returns a single-cell address, never a range.
  • To get only the column letter, use =SUBSTITUTE(ADDRESS(1, col, 4), "1", "").
  • Related functions: ROW, COLUMN, INDIRECT, MATCH, INDEX, CELL.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.