ADDRESSformula in Google Sheets returns a cell reference as a string, given the row and column numbers. It can also return an absolute or relative reference, and can include sheet name in the reference. This formula is most commonly used to create dynamic references in other formulas or to create a cell reference based on certain criteria.
- How to use
- Examples of using
ADDRESSformula not working?
- Similar formulas to
ADDRESS formula with the syntax shown below, it has 2 required parameters and 3 optional parameters:
- row (required):
The row number of the cell reference, starting from 1 (integer).
- column (required):
The column number of the cell reference, starting from 1 (integer).
- 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).
- 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.
- 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.
ExamplesHere are a few example use cases that explain how to use the
ADDRESSformula in Google Sheets.
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
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.
ADDRESSnot working? Here are some common mistakes people make when using the
ADDRESSGoogle Sheets Formula:
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.
The following functions are similar to
ADDRESS or are often used with it in a formula:
INDIRECTformula 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
INDIRECTformula can also be used to reference cells in other sheets or workbooks.
OFFSETformula 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.
MATCHformula 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
INDEXformula to retrieve a value from a specific cell in a range. The
search_typeparameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.
INDEXformula 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.
You can learn more about the
ADDRESS Google Sheets function on Google Support.