Lookup & Reference

ADDRESS Function in Excel

Creates a cell reference as text from row and column numbers.

Syntax

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

Arguments

  • row_num (required): Row number for the reference
  • column_num (required): Column number for the reference
  • abs_num (optional): 1=absolute, 2=abs row, 3=abs col, 4=relative
  • a1 (optional): TRUE=A1 style, FALSE=R1C1 style
  • sheet_text (optional): Sheet name to include

Examples

  • =ADDRESS(2, 3) - Create reference to row 2, col 3 - Result: $C$2
  • =ADDRESS(2, 3, 4) - Relative reference - Result: C2

When to use ADDRESS

  • Use ADDRESS when you need to retrieve values from a table or range based on a key.
  • Confirm data types match (text vs number) and trim spaces before lookups.
  • Pair with [IFERROR](/functions/iferror/) or [IFNA](/functions/ifna/) for cleaner reports.
  • See the [lookup functions guide](/guides/excel-lookup-functions-guide/) for VLOOKUP, XLOOKUP, and INDEX MATCH paths.

Common ADDRESS mistakes

  • #N/A often means the key is missing or exact match is wrong — see [#N/A error](/errors/na/).
  • Lock table references with $ when copying formulas down a column.
  • Compare legacy vs modern lookups in [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Common errors

  • Invalid abs_num value

Use cases

  • Dynamic cell references
  • Building formulas
  • Cross-sheet references

Frequently asked questions

  • How do I create a dynamic reference to another sheet? Use the sheet_text parameter: =ADDRESS(1, 1, 1, TRUE, "Sheet2") returns "Sheet2!$A$1". Combine with INDIRECT to actually reference that cell: =INDIRECT(ADDRESS(1, 1, 1, TRUE, "Sheet2")).
  • What do the abs_num values mean? 1 = $A$1 (fully absolute), 2 = A$1 (absolute row only), 3 = $A1 (absolute column only), 4 = A1 (fully relative). Use 4 for relative references that adjust when copied.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.