Summary

The Excel ADDRESS function creates a cell reference as text, based on a given row and column number. It can generate absolute, mixed, or relative references in either A1 or R1C1 style, and can optionally include a sheet name. ADDRESS is most powerful when combined with INDIRECT to build fully dynamic formulas that respond to changing inputs.

Syntax

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

ADDRESS takes two required arguments and three optional ones. row_num and col_num are always numbers — row 1 is the first spreadsheet row, column 1 maps to column A. The optional abs_num controls dollar-sign locking: 1 locks both row and column ($A$1), 4 locks neither (A1), and 2/3 lock one dimension only. The a1 flag switches between A1 and R1C1 notation (default is A1). Finally, sheet_text lets you prefix the address with a sheet name, producing a cross-sheet reference like 'Sales'!$A$1.

Arguments

• row_num — The row number. Row 1 is the first row.
• col_num — The column number. Column 1 = A, Column 2 = B, and so on.
• abs_num — [optional] Reference type (1=absolute, 2=abs row/rel col, 3=rel row/abs col, 4=relative). Default: 1.
• a1 — [optional] Style: TRUE = A1 style (default), FALSE = R1C1 style.
• sheet_text — [optional] Worksheet name to include in the reference.

ADDRESS vs Other Functions

Think of these functions as a workflow: some functions build references, some resolve references, and some return values or positions. Using the right one at the right step keeps your models clean and scalable.

Overall, the table shows that these functions play different roles in one reference workflow: ROW, COLUMN, and MATCH help you determine coordinates; ADDRESS converts those coordinates into a reference string; and INDIRECT resolves that string into a live reference Excel can calculate. If your goal is simply to return a value from a position, INDEX is usually the most direct and stable path. If your model requires dynamic, text-driven references (including sheet-aware references), then ADDRESS combined with INDIRECT provides the most flexible pattern.

Using ADDRESS

ADDRESS acts as a reference builder. Instead of typing "$B$3" directly into a formula, you describe the cell by its row and column numbers and let ADDRESS construct the address for you. This becomes invaluable when those row and column numbers come from calculations, user inputs, or other formulas — making your workbooks truly dynamic.

Keep in mind: ADDRESS always returns a text string, not a live cell reference. If you need to actually retrieve the value at that address, you must wrap it inside INDIRECT, which is the function that converts a text address back into a real reference Excel can evaluate.

• Use ADDRESS when you need to build a reference from calculated numbers.
• Use ADDRESS + INDIRECT when you need to retrieve the value at a dynamically built location.
• Combine with MATCH to find column positions by header name rather than hardcoding column numbers.

Example 1 — Building an Absolute Reference

The most basic use: pass a row and column number to get a default absolute reference. Both row and column are locked with dollar signs, making this reference safe to copy anywhere in the spreadsheet without it shifting.

=ADDRESS(3, 2)    // Returns "$B$3"
=ADDRESS(1, 1)    // Returns "$A$1"
=ADDRESS(10, 26)  // Returns "$Z$10"

Example 2 — Controlling Reference Type with abs_num

Pass a third argument to control how the reference is locked. A value of 1 (the default) locks both dimensions. A value of 4 makes the reference fully relative — useful when you want the generated reference to behave the same way a manually typed relative reference would when copied.

=ADDRESS(5, 3, 1)  // "$C$5" — absolute (default)
=ADDRESS(5, 3, 2)  // "C$5"  — absolute row, relative column
=ADDRESS(5, 3, 3)  // "$C5"  — relative row, absolute column
=ADDRESS(5, 3, 4)  // "C5"   — fully relative

Example 3 — Including a Sheet Name

Add the optional sheet_text argument to create a cross-sheet reference. This is especially useful when building formulas that pull data from multiple worksheets dynamically — instead of hardcoding the sheet name inside the formula, you can reference a cell that contains the sheet name and drive it from there.

=ADDRESS(1, 1, 1, TRUE, "Sales")      // "'Sales'!$A$1"
=ADDRESS(2, 4, 4, TRUE, "Dashboard")  // "'Dashboard'!D2"

Note: Excel automatically wraps sheet names containing spaces in single quotes.

Example 4 — Combining ADDRESS with INDIRECT

This is where ADDRESS truly shines. On its own, =ADDRESS(3, 2) gives you the string "$B$3" — just text. By wrapping it in INDIRECT, Excel evaluates that string as a real cell reference and returns whatever value lives in B3. The target cell is now determined at runtime, driven by other values in your spreadsheet.

=INDIRECT(ADDRESS(3, 2))
// Returns the actual VALUE stored in cell B3

=INDIRECT(ADDRESS(A1, B1))
// If A1=2 and B1=3, returns the value from cell C2

=INDIRECT(ADDRESS(5, MATCH("Revenue", A1:Z1, 0)))
// Finds "Revenue" column dynamically, returns row 5's value

Excel documentation emphasizes that ADDRESS returns text, not a live reference. In production models, this means ADDRESS is usually paired with INDIRECT only when dynamic text-driven references are truly required.

• Use ADDRESS when your row/column coordinates are calculated.
• Use INDEX when you only need values and want a non-volatile approach.
• Use sheet_text carefully for cross-sheet dynamic navigation.

A maintainable pattern is: derive position with MATCH, build address with ADDRESS, and resolve only when needed.

Conclusion Recap

• Summary: ADDRESS builds a cell reference as text from row and column numbers.
• Syntax: =ADDRESS(row_num, col_num, [abs_num], [a1], [sheet_text]) gives you flexible control over output format.
• Arguments: abs_num controls lock style, a1 switches notation, and sheet_text targets a specific sheet.
• Core behavior: ADDRESS returns text only; pair with INDIRECT to read the real value.
• Practical usage: Absolute, relative, and cross-sheet references become dynamic and maintainable when inputs change.
• Best pattern: Combine MATCH + ADDRESS + INDIRECT for robust, column-safe lookup workflows.