Excel functions / Lookup

ADDRESS function

Builds a cell address as text from a row number and a column number.

Syntax

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

Arguments

When to use it

ADDRESS builds a cell address as text from a row number and a column number: ADDRESS(2, 3) is "$C$2". A third argument chooses which parts get a $ sign.

The text can be turned into a real reference with INDIRECT.

Examples

  1. Row 2, column 3

    ABCDE
    1Value
    2$C$2

    In E2: =ADDRESS(2, 3) → $C$2

    Try this example

  2. Without the $ signs

    ABCDE
    1Value
    2C2

    In E2: =ADDRESS(2, 3, 4) → C2

    Try this example

  3. Where the largest sale is

    ABCDE
    1RegionRepSales
    2NorthAna1200$C$2
    3SouthBen800
    4NorthChloe900
    5WestAna400
    6NorthAna300

    In E2: =ADDRESS(MATCH(MAX(C2:C6), C2:C6, 0)+1, 3) → $C$2

    Try this example

Common mistakes

ADDRESS in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try ADDRESS in the playground

Related functions