Definition:
The Excel formula “ADDRESS” returns a cell reference as a text string, based on the row and column number of a cell.
Purpose:
The purpose of the “ADDRESS” formula is to get the address of a cell in a text format.
Syntax:
The syntax of the “ADDRESS” formula is
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
Parameters:
- row_num: Required. It specifies the row number of the cell reference.
- column_num: Required. It specifies the column number of the cell reference.
- abs_num: Optional. It specifies the type of reference, i.e., relative, or absolute. Default is 1. Below is the list of other abs_num
- 1 : Absolute referencing. For example: $A$1
- 2 : Relative column; absolute row. For example: A$1
- 3 : Absolute column; relative row. For example: $A1
- 4 : Relative referencing. For example: A1
- a1: Optional. It specifies the reference style, i.e., A1 or R1C1. Default is TRUE.
- sheet_text: Optional. It specifies the sheet name of the cell reference. Default is the current sheet.
Returns:
The “ADDRESS” formula returns a cell reference in a text format.
Availability:
The “ADDRESS” formula is available in all versions of Excel.
Example-1:
=ADDRESS(1, 1)
returns $A$1.
Example-2:
=ADDRESS(3, 2, , , “Sheet2”)
returns Sheet2!$B$3.
Example-3:
=ADDRESS(1, 2, 4)
returns $B$1.
Example-4:
=ADDRESS(3, 3, 1, FALSE)
returns C3.
Example-5:
=ADDRESS(1, 1, 4, TRUE, "Sheet2")
returns ‘Sheet2’!$A$1.
Tips and tricks:
- Use the “ADDRESS” formula with other formulas to make dynamic formulas.
- Use the “INDIRECT” formula with “ADDRESS” to get the value of a cell whose reference is in a text format.
Limitations:
The “ADDRESS” formula returns a static cell reference, which means it does not change if you copy or move the formula.
Common errors and solutions:
- #VALUE! error: Occurs when the row_num or column_num is not a valid number. Check if the values are correct.
- #REF! error: Occurs when the abs_num is greater than 4. Check the abs_num value.
List of Related functions:
- ROW: Returns the row number of a cell.
- COLUMN: Returns the column number of a cell.
- INDIRECT: Returns the value of a cell whose reference is in a text format.
- OFFSET: Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
Frequently Used with the formulas:
The “ADDRESS” formula is frequently used with other formulas like “INDIRECT“, “SUMIF“, and “VLOOKUP” to create dynamic formulas.
Frequently Asked Questions:
Q: What is the purpose of the “abs_num” parameter in the “ADDRESS” formula?
A: The “abs_num” parameter specifies the type of reference, i.e., relative, or absolute.
Q: Can the “ADDRESS” formula return the cell reference in R1C1 style?
A: Yes, by setting the a1 parameter to FALSE, you can get the cell reference in R1C1 style.
