How to use ADDRESS Function in Excel

Address function in excel is used to find the address of the cell and the value returned by this function is absolute, it is an inbuilt function, this function has two mandatory arguments which is the row number and the column number, for example, if we use =Address(1,2) we will get output as $B$1.

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

The ADDRESS function syntax has the following arguments:

  • row_num    Required. A numeric value that specifies the row number to use in the cell reference.
  • column_num    Required. A numeric value that specifies the column number to use in the cell reference.
  • abs_num    Optional. A numeric value that specifies the type of reference to return.
abs_num Returns this type of reference
1 or omitted Absolute
2 Absolute row; relative column
3 Relative row; absolute column
4 Relative
  • A1    Optional. A logical value that specifies the A1 or R1C1 reference style. In A1 style, columns are labeled alphabetically, and rows are labeled numerically. In R1C1 reference style, both columns and rows are labeled numerically. If the A1 argument is TRUE or omitted, the ADDRESS function returns an A1-style reference; if FALSE, the ADDRESS function returns an R1C1-style reference.
  • sheet_text    Optional. A text value that specifies the name of the worksheet to be used as the external reference. For example, the formula =ADDRESS(1,1,,,”Sheet2″) returns Sheet2!$A$1. If the sheet_text argument is omitted, no sheet name is used, and the address returned by the function refers to a cell on the current sheet.

Example: Let’s look at some Excel ADDRESS function examples and explore how to use the ADDRESS function as a worksheet function in Microsoft Excel:

READ:  How to use the Excel MATCH function

ADDRESS Function - How to use ADDRESS Function in Excel

Syntax:  =ADDRESS(A2,B2,C2)

Result:

ADDRESS Function in Excel - How to use ADDRESS Function in Excel

Based on the Excel spreadsheet above, the following ADDRESS examples would return:

Syntax: =ADDRESS(A3,B3,C3)
Result: A$1

Syntax: =ADDRESS(A4,B4,C4)
Result: $A1

Syntax: =ADDRESS(A5,B5,C5)
Result: A1

Syntax: =ADDRESS(A6,B6)
Result: $O$12

Syntax: =ADDRESS(A7,B7)
Result: $E$100

Syntax: =ADDRESS(A8,B8,C8,FALSE)
Result: R2C[3]

Syntax: =ADDRESS(A8,B8,C8,FALSE,”Cost”)
Result: Cost!R2C[3]

Note:

The ADDRESS function is used to create an address from a given row and column number.

Depending upon the need set the Absolute parameter to one of the following

  • 1 or omitted, Absolute reference
  • 2, Absolute row; relative column
  • 3, Relative row; absolute column
  • 4, Relative reference

Do not forget to set the second parameter i.e. reference type of the INDIRECT function to zero or False when Reference style is the R1C1 type.

Add a Comment

Your email address will not be published. Required fields are marked *