Summary

The Excel ADDRESS function generates a cell’s address based on specific row and column numbers. For instance, =ADDRESS(1,1) produces $A$1. The function can provide addresses in various formats (relative, mixed, or absolute) and is useful for creating cell references within formulas.

Syntax

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

row_num: The row number for the cell address.
col_num: The column number for the cell address.
abs_num: [Optional] Type of address (absolute, relative, etc.). Defaults to absolute (1).
a1: [Optional] Reference style (A1 vs. R1C1). Defaults to A1 style.
sheet: [Optional] Worksheet name. Defaults to the current sheet.

Return value

A string representing a cell address from the current or a specified worksheet.

How to use

The ADDRESS function creates a cell address from specified row and column numbers. It can return references in relative, mixed, or absolute formats. The function’s output is a text representation of a reference, which you can convert into an actual reference with INDIRECT if needed. While ADDRESS is useful for certain tasks, functions like INDEX and MATCH are typically more straightforward for retrieving values at specific locations.

The abs_num argument allows various types of address formatting:

1 or omitted: Absolute ($A$1)
2: Absolute row, relative column (A$1)
3: Relative row, absolute column ($A1)
4: Relative (A1)

Examples

Simple ADDRESS

Creating a Basic Cell Address:

				
					=ADDRESS(1,1)

This formula generates the cell address $A$1.

ADDRESS for Relative Reference

Generating a Relative Cell Reference:

				
					=ADDRESS(1,1,4)

Here, the ADDRESS function returns A1, illustrating a relative reference.

ADDRESS for Mixed Reference

Creating a Mixed Cell Reference:

				
					=ADDRESS(100,26,2)

This example demonstrates how to create a mixed reference, resulting in Z$100.

ADDRESS for R1C1 Style Address

Using R1C1 Reference Style:

				
					=ADDRESS(1,1,1,FALSE)

This formula returns R1C1, using the R1C1 reference style instead of the default A1 style.

ADDRESS with Sheet Name

Incorporating a Worksheet Name in the Address:

				
					=ADDRESS(1,1,4,TRUE,"Sheet1")

This formula produces Sheet1!A1, showing how to include a specific worksheet name in the cell address.

Additional Notes

The ADDRESS function is useful for dynamically creating cell references in formulas.
By default, ADDRESS returns absolute references ($A$1). The abs_num argument can change this to relative (A1), mixed (A$1 or $A1), or other combinations.
When the a1 argument is set to FALSE, ADDRESS generates R1C1 style references, which can be more intuitive in certain programming or formula contexts.
The sheet argument allows incorporating a specific worksheet’s name into the address. This is particularly useful when dealing with multiple sheets.
ADDRESS returns a text representation of the cell address. To use this address in a formula, like for range referencing, it needs to be converted into a proper reference with the INDIRECT function.
While ADDRESS is powerful for certain specialized tasks, simpler functions like INDEX and MATCH are often more straightforward for retrieving data from specific row and column locations.

Related Functions

Excel MATCH function

The Excel MATCH function finds the position of a value within a range, essential for dynamic lookups and complex searches.

Excel INDEX function

The Excel INDEX function retrieves values at a specific row and column in a range, key for dynamic data lookups and retrieval.

Excel INDIRECT function

The Excel INDIRECT function converts text strings into dynamic cell references, enabling flexible and adaptable data interactions.

Excel ADDRESS function