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"
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
Arguments
| Argument | Required? | What it does |
|---|---|---|
| row_num | Yes | The row number of the cell (1 = row 1). |
| column_num | Yes | The column number of the cell (1 = column A, 3 = column C, 28 = column AB). |
| abs_num | Optional | Which parts are locked with $. Defaults to 1 (fully absolute). See the table below. |
| a1 | Optional | TRUE (default) for A1 style like $C$5. FALSE for R1C1 style like R5C3. |
| sheet_text | Optional | A 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:
| A | B | C | |
|---|---|---|---|
| 1 | abs_num | Formula | Result |
| 2 | 1 (default) | =ADDRESS(5,3) | $C$5 |
| 3 | 2 | =ADDRESS(5,3,2) | C$5 |
| 4 | 3 | =ADDRESS(5,3,3) | $C5 |
| 5 | 4 | =ADDRESS(5,3,4) | C5 |
| 6 | 1, R1C1 style | =ADDRESS(5,3,1,FALSE) | R5C3 |
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.
| A | B | C | D | |
|---|---|---|---|---|
| 4 | Row | Column | Address | |
| 5 | 1 | 1 | $A$1 | |
| 6 | 5 | 3 | $C$5 | |
| 7 | 10 | 28 | $AB$10 |
The formula in D5 (copied down) is:
Breaking it down:
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.
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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Top sale is in | ||
| 2 | Ana | 4,200 | $B$4 | ||
| 3 | Ben | 3,150 | |||
| 4 | Cara | 6,800 | |||
| 5 | Dev | 5,020 | |||
| 6 | Eli | 2,990 |
- MAX(B2:B6) finds the highest sale: 6,800.
- MATCH(…, B2:B6, 0) finds its position in the list: 3rd.
- We add 1 because the list starts in row 2, not row 1 — so the real row is 4.
- ADDRESS(4, 2) returns $B$4.
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:
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.
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.

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.