ADDRESS

Formulas / ADDRESS
Return the address of a cell using a row and column number.
=ADDRESS(row_num, col_num, [abs_num], [a1], [sheet])
  • row_num - numeric value representing row number
  • column_num - numeric value representing column number
  • abs_num - [OPTIONAL] numeric value that specifies whether to return an absolute reference or not (defaults to 1)
  • A1 - [OPTIONAL] boolean that determines if A1 or R1C1 reference style is used
  • sheet_text - [OPTIONAL] text string that determines the worksheet name to use

Examples

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

    The ADDRESS function can be used to convert a row and column number into an address. The formula above, will return Sheet1!A1. This means that the cell in the first row and first column on Sheet1 will be returned as the address.

  • =ADDRESS(1,1)

    The ADDRESS function can also be used to return an address without the sheet name. For example,the formula above will return $A$1. This means that the cell in the first row and first column will be returned as the address.

  • =ADDRESS(1,1,4)

    The ADDRESS function can also be used to specify the format of the address. For example, the formula above will return A1. This means that the cell in the first row and first column will be returned as the address in the A1 formatting.

  • =ADDRESS(100,26,4)

    The ADDRESS function can also be used to return an address with a larger row and column number. For example, this example formula will return Z100.

Summary

The ADDRESS function returns the address of a cell in a worksheet given the row and column numbers specified. It can also generate either an absolute or relative reference depending on the abs_num argument.

  • The ADDRESS function returns a cell address by using a given row and column number, and can return addresses in relative, mixed, or absolute formats.
  • The ADDRESS function can construct a cell reference within a formula, and outputs a text value.
  • The text that ADDRESS returns must be converted into a proper reference to use it in a formula, which can be done using the INDIRECT function.
  • The INDEX function can be used to get the value at an address returned by ADDRESS.


Frequently Asked Questions

What is the ADDRESS function?
The ADDRESS function outputs the address of a cell based on row and column numbers that are input into the function.
What arguments does the ADDRESS function require?
The ADDRESS function requires row and column numbers as inputs. Additionally, you can use the ABS_NUM argument to determine how the ADDRESS function returns its reference.
What are the return types for the ADDRESS function?
When the A1 argument is set to TRUE, the ADDRESS functions returns an A1 reference. When the A1 argument is set to FALSE, the ADDRESS function returns an R1C1 reference.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV