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
row_num: The row number.column_num: The column number: 1 is A.abs_num(optional): 1 $A$1 (default), 2 A$1, 3 $A1, 4 A1.a1(optional): TRUE (default) for A1 style; FALSE for R1C1.sheet_text(optional): A sheet name to put in front.
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
Row 2, column 3
A B C D E 1 Value 2 $C$2 In E2:
=ADDRESS(2, 3)→ $C$2Without the $ signs
A B C D E 1 Value 2 C2 In E2:
=ADDRESS(2, 3, 4)→ C2Where the largest sale is
A B C D E 1 Region Rep Sales 2 North Ana 1200 $C$2 3 South Ben 800 4 North Chloe 900 5 West Ana 400 6 North Ana 300 In E2:
=ADDRESS(MATCH(MAX(C2:C6), C2:C6, 0)+1, 3)→ $C$2
Common mistakes
- Expecting a value. ADDRESS gives the address as text, not what is in the cell. Wrap it in INDIRECT to read the cell, or use INDEX directly.
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.
- Czech: ODKAZ
- Danish: ADRESSE
- Dutch: ADRES
- Finnish: OSOITE
- French: ADRESSE
- German: ADRESSE
- Hungarian: CÍM
- Italian: INDIRIZZO
- Norwegian: ADRESSE
- Polish: ADRES
- Portuguese (Brazil): ENDEREÇO
- Portuguese (Portugal): ENDEREÇO
- Russian: АДРЕС
- Spanish: DIRECCION
- Swedish: ADRESS
- Turkish: ADRES
Related functions
- INDIRECT: Turns text such as "B2" into a real reference to that cell or range.
- ROW: The row number of a reference (or of the current cell).
- COLUMN: The column number of a reference (A = 1).
- CELL: Tells you something about a cell: its address, row, column, contents, number format or the type of value in it.
- MATCH: The position of a value in a row or column.