Knowledge base / Excel

ADDRESS in Excel: return a cell address as text

I distinguish address text from a cell value and show why a relative position may need an offset before it becomes a worksheet row.

Kajo RudzińskiPublished Updated 4 min read

Guide rails identify one cell in a coordinate grid

You have a row number and a column number and want to show the address of the selected cell. ADDRESS does that. I separate describing a location from retrieving the value stored there. ADDRESS performs the first task and returns text; that distinction makes the examples much easier to reason about.

Syntax and reference types

Microsoft documents this argument order:

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

The first two arguments identify a worksheet row and column. The third selects absolute or relative notation. The fourth chooses A1 or R1C1 style, and the last adds a sheet name. Square brackets in the syntax description indicate optional arguments. Do not type those brackets into the actual formula.

Formula Returned text
=ADDRESS(3,2) $B$3
=ADDRESS(3,2,2) B$3
=ADDRESS(3,2,3) $B3
=ADDRESS(3,2,4) B3
=ADDRESS(10,4,1,FALSE) R10C4

The Polish documentation uses ADRES and calls the alternative style W1K1. In Polish Excel the final example returns W10K4. Function names, argument separators and the row-and-column notation therefore need to match the environment in which the workbook is used.

Dollar signs are part of the returned string. Choosing type 4 does not by itself create an active cell reference that Excel will move during copying. Establish whether you need a displayed location label or a calculation involving the data at that location. The same-looking address can play a different role in each task.

Address text and the cell’s value

For a synthetic example, put 25 in B3. The formula =ADDRESS(3,2) displays $B$3, rather than 25. To interpret the string as a reference, you can use INDIRECT:

=INDIRECT(ADDRESS(3,2))

The result is 25. Microsoft documents INDIRECT as returning a reference specified by text. Its Polish name is ADR.POŚR; INDIREKT in the previous Polish entry was incorrect. If all you need is this fixed cell, =B3 is considerably easier to read.

Combining functions becomes useful when another calculation supplies the row or column number. I do not add this layer to every reference merely because it looks advanced. Someone reviewing the workbook should be able to identify where a value comes from without reconstructing several layers of text-based addresses.

A card describes a grid location while a pointer identifies its contents

A position inside a range differs from a worksheet row

Put the header Amount in A1 and synthetic values 10, 30 and 20 in A2:A4. To return the address of the first largest value, use:

=ADDRESS(MATCH(MAX(A2:A4),A2:A4,0)+1,1)

The largest value is 30. MATCH finds it at position 2 within A2:A4. Adding 1 converts that position to worksheet row 3, because the range begins on row 2. Column 1 is A, so the returned text is $A$3. The MATCH reference explains relative positions and the first exact match when the match type is 0.

That offset is correct for this layout. Moving the data to A10:A12 requires a different adjustment. Do not copy the formula to an arbitrary area without checking where its input range begins. If the maximum appears more than once, this pattern identifies the first exact match, rather than returning every tied location.

Constructing a range string and adding a sheet name

Join two addresses with a colon to describe a range:

=ADDRESS(1,1)&":"&ADDRESS(10,3)

The result is the text $A$1:$C$10. No extra conversion function is required. ZRÓB.TEKST, used in the previous Polish entry, was not a valid Excel function name for this operation. Concatenating the addresses does not itself sum the range or retrieve its values.

To add a sheet name, enter =ADDRESS(3,2,1,TRUE,"Data"). The result is Data!$B$3. This argument describes the sheet, but generating its name does not prove that the sheet exists. If you later use the string as a reference, check the source name and availability. The INDIRECT documentation describes restrictions on external references.

Choose the tool for the required output

If you need a value at a specified position in a known range, consider INDEX. Microsoft describes its purpose here. ADDRESS is particularly useful when the intended output is a readable location to inspect or a string required by another step.

For practice, change 30 to 5 and predict the address of the new maximum. Then move the header and data further down the sheet and work out the new offset. Compare the returned text with the actual target cell. This catches a common problem: an address can be syntactically valid while pointing one row above the intended value.

Key takeaways

Does ADDRESS return a cell value?

No. It returns address text. Retrieve the value through another reference, for example with INDIRECT.

Does MATCH return a worksheet row number?

It returns a position within its lookup range. Account for the range starting row when constructing an address.

How do I request relative notation?

Use abs_num 4. For example, ADDRESS(3,2,4) returns the text B3.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.