ADDRESS

• How to use ADDRESS formula?
• Examples of using ADDRESS formula
• ADDRESS formula not working?
• Similar formulas to ADDRESS

Usage

Use the ADDRESS formula with the syntax shown below, it has 2 required parameters and 3 optional parameters:

=ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

1. row (required):
   The row number of the cell reference, starting from 1 (integer).
2. column (required):
   The column number of the cell reference, starting from 1 (integer).
3. absolute_relative_mode (optional):
   Optional. A number that specifies the reference type: 1 for absolute, 2 for relative, 3 for mixed row and absolute column, 4 for absolute row and mixed column (integer). Default is 1 (absolute).
4. use_a1_notation (optional):
   Optional. A logical value that specifies whether to use A1 notation (TRUE) or R1C1 notation (FALSE) for the reference. Default is TRUE.
5. sheet (optional):
   Optional. A text value that specifies the sheet name to include in the reference. If omitted, the reference will be to the current sheet.

Examples

Creating dynamic references

By using the ADDRESS formula in combination with other formulas, you can create dynamic references that change based on certain criteria. For example, you can use it to create a reference to a cell that is a certain number of rows or columns away from another cell, or to create a reference to a cell based on the value in another cell.

Creating range references

The ADDRESS formula can also be used to create range references by combining it with other formulas. For example, you can use it to create a reference to an entire column or row by referencing the first and last cells in the column or row.

Creating cell references with sheet names

If you have multiple sheets in your spreadsheet, you can use the ADDRESS formula to create cell references that include the sheet name. This can be useful if you want to reference cells in other sheets from a formula.

Common Mistakes

Missing arguments

Users may forget to include all required arguments, such as the row and column numbers.

Incorrect cell reference

Users may reference the wrong cell or range, causing the formula to return an error or incorrect result.

Incorrect use of absolute/relative mode

Users may misunderstand how to use the absolute/relative mode argument, causing the formula to return unexpected results.

Incorrect use of A1 notation

Users may misunderstand how to use the A1 notation argument, causing the formula to return unexpected results.

Incorrect sheet reference

Users may reference the wrong sheet name or index, causing the formula to return an error or incorrect result.

Related Formulas

The following functions are similar to ADDRESS or are often used with it in a formula:

• INDIRECT

  The INDIRECT formula is used to return a cell reference specified by a string. This can be useful when you need to dynamically reference a cell based on the value in another cell. The INDIRECT formula can also be used to reference cells in other sheets or workbooks.

• OFFSET

  The OFFSET formula in Google Sheets returns a cell or range of cells that is a specified number of rows and columns from a starting cell reference. This formula is most commonly used to create dynamic ranges that can expand or contract as data is added or removed from a sheet.

• MATCH

  The MATCH formula is used to search for a specified value within a range and returns the relative position of the value within the range. This formula is commonly used in conjunction with the INDEX formula to retrieve a value from a specific cell in a range. The search_type parameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.

• INDEX

  The INDEX formula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells.

Learn More

You can learn more about the ADDRESS Google Sheets function on Google Support.