In Excel: use =ADDRESS(row_num, column_num) — it builds a cell reference as TEXT, so =ADDRESS(3, 2) returns the string "$B$3".
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
row_num | required | The row number. |
column_num | required | The column number — 1 is A. |
abs_num | optional | 1 absolute ($A$1), 2 absolute row, 3 absolute column, 4 relative. |
a1 | optional | TRUE or omitted for A1 style, FALSE for R1C1. |
sheet_text | optional | A sheet name to prefix, quoted automatically if it contains spaces. |
Related functions
Select the result cell and type =ADDRESS(.
Enter the row number, a comma, then the column number — 1 for A, 2 for B.
Add a third argument of 4 if you want a relative reference rather than the absolute default.
Wrap the whole thing in INDIRECT if you need the value rather than the address.
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
What this does
ADDRESS constructs a cell reference as a text string from a row and column number. The critical word is text: the result is the characters "$B$3", not a live reference to that cell, so it cannot be used to fetch a value on its own. Feeding it to INDIRECT converts it into a real reference, which is the usual pairing. Its most useful role is diagnostic — combined with MATCH it reports where a value was found, which is exactly what you want when a lookup returns something unexpected and you need to see which row it came from. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel address function”. Start on a copy or a tiny sample, keep the affected formula visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a calculation you can defend to a CFO or an auditor, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
Reporting where the maximum sits: =ADDRESS(MATCH(MAX(B2:B200), B2:B200, 0)+1, 2) returns a string such as "$B$147". To actually read that cell, wrap it: =INDIRECT(ADDRESS(147, 2)). For a relative reference without the dollar signs, pass 4 as the third argument. ADDRESS turns a lookup result into a location you can point at, which is what makes an unexpected answer debuggable. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
In Google Sheets
Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. Here is the takeaway for “excel address function”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.
Common mistakes
- Expecting ADDRESS to return the cell's value — it returns text, and needs
INDIRECTto become a live reference. - Passing a column letter rather than a number; the second argument is numeric.
- Forgetting the header offset when combining with
MATCH, which reports a position within the range rather than a sheet row.
Frequently asked questions
How do I get the value at an address?
Wrap it: =INDIRECT(ADDRESS(row, col)). ADDRESS alone returns only the text of the reference.
How do I get a relative reference?
Pass 4 as the third argument: =ADDRESS(3, 2, 4) returns "B3" instead of "$B$3".
How do I find the address of a lookup result?
Combine with MATCH and add the offset of your range's first row: =ADDRESS(MATCH(x, range, 0)+offset, col).