ADDRESS

 The ADDRESS function in Excel is used to create a cell address as a text string, based on specified row and column numbers. It can be useful for dynamic referencing in formulas. Below are 10 examples of how to use the ADDRESS function, including its application with data and formulas.

---

Basic Syntax of ADDRESS

excel

Copy

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

• row_num: The row number for the cell reference.

• column_num: The column number for the cell reference.

• [abs_num]: Optional. Specifies the type of reference:

  • 1 or omitted: Absolute reference (e.g., $A$1).

  • 2: Absolute row, relative column (e.g., A$1).

  • 3: Relative row, absolute column (e.g., $A1).

  • 4: Relative reference (e.g., A1).

• [a1]: Optional. TRUE (default) for A1-style referencing, FALSE for R1C1-style.

• [sheet_text]: Optional. Adds a sheet name to the address (e.g., Sheet1!A1).

---

10 Examples of ADDRESS Function

1. Basic Absolute Reference

excel

Copy

=ADDRESS(5, 3)

• Result: $C$5 (absolute reference to cell C5).

---

2. Relative Reference

excel

Copy

=ADDRESS(5, 3, 4)

• Result: C5 (relative reference to cell C5).

---

3. Mixed Reference (Absolute Row, Relative Column)

excel

Copy

=ADDRESS(5, 3, 2)

• Result: C$5 (absolute row, relative column).

---

4. Mixed Reference (Relative Row, Absolute Column)

excel

Copy

=ADDRESS(5, 3, 3)

• Result: $C5 (relative row, absolute column).

---

5. Include Sheet Name

excel

Copy

=ADDRESS(5, 3, 1, TRUE, "Sheet2")

• Result: Sheet2!$C$5 (absolute reference to cell C5 on Sheet2).

---

6. Dynamic Row and Column Numbers

excel

Copy

=ADDRESS(ROW(A5), COLUMN(C1))

• Result: $C$5 (uses ROW and COLUMN functions to dynamically determine the row and column).

---

7. Use with INDIRECT to Retrieve Cell Value

excel

Copy

=INDIRECT(ADDRESS(5, 3))

• Result: Returns the value in cell C5.

---

8. Create a Range Using Two ADDRESS Functions

excel

Copy

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

• Result: $A$1:$C$5 (creates a range from A1 to C5).

---

9. Use with MATCH and INDEX for Dynamic Lookup

excel

Copy

=INDEX(A1:C10, MATCH("Value", A1:A10, 0), 3)

• Combine with ADDRESS to get the address of the matched cell:

excel

Copy

=ADDRESS(MATCH("Value", A1:A10, 0), 3)

• Result: Returns the address of the cell in column C where "Value" is found in column A.

---

10. Dynamic Table Reference

excel

Copy

=ADDRESS(ROW(Table1[Column1]), COLUMN(Table1[Column2]))

• Result: Returns the address of the cell at the intersection of the current row in Table1[Column1] and Table1[Column2].

---

Practical Use Cases

1. Dynamic Cell Referencing: Use ADDRESS with INDIRECT to dynamically reference cells based on row and column numbers.

2. Creating Named Ranges: Combine ADDRESS with other functions to define dynamic named ranges.

3. Lookup Formulas: Use ADDRESS to return the location of a matched value in a lookup formula.

4. Data Validation: Generate cell references dynamically for dropdown lists or conditional formatting.