Easy Explanation Notes with Examples

ADDRESS function in Excel


The ADDRESS function returns a cell reference, if specified cell and row numbers are provided. For example, ADDRESS(2,2) returns $B$2.


ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

The ADDRESS function has the following arguments:

row_num – Required. The row number that you want to use in the cell reference.

column_num – Required. The column number to use in the cell reference.

[abs_num] – Optional. The reference type i.e. absolute, relative or mixed. If omitted default is absolute. Possible values are:

abs_numReturns this type of reference
1 or omittedAbsolute
2Absolute row; relative column
3Relative row; absolute column

[a1] – Optional. A optional argument that specifies the A1 or R1C1 reference style. If omitted, Excel returns A1 reference style.

A1 styleR1C1 style
Columns are labeled alphabetically, and rows are labeled numerically. e.g. B$2 if row number is 2, column number is 2 and abs_num 2. Both columns and rows are labeled numerically. e.g. R2C[2] against row 2, column 2 and abs_num 2.

sheet_text – Optional. In case of external reference, the argument specifies the name of the worksheet. If omitted no sheet name is used.


Function Errors

The function commonly returns the #VALUE!, possible reasons for the error are:

-If either the row or column number is less than 1 or greater than the number of rows/columns in a worksheet.

-If any supplied argument is non-numeric.

-If supplied values against the [a1] argument are other than 1, 2, 3 and 4.

Copyright © 2016 - 2018 Explainry.com | All Rights Reserved