ADDRESS Function
A Lookup and Reference function that returns a cell text address based on a provided row and column number
What is the Cell ADDRESS Function?
The ADDRESS function is a Lookup and Reference function that returns a cell text address based on a provided row and column number.
Financial professionals less commonly use the function than some of the other lookup and reference functions, such as the XLOOKUP, the VLOOKUP, and the HLOOKUP. Nevertheless, it could be helpful when dealing with large data sets and thus worth exploring.
The ADDRESS function often goes hand in hand with the CELL function. Using these two functions, you can obtain various information, including the address, the reference, the formula, and much more about any cell in a worksheet.
The ADDRESS function is a built-in worksheet function that can be utilized once familiar with its syntax. In other words, on top of utilizing the Formulas tab, you can also click on any cell in a worksheet and start typing the syntax.
Key Takeaways
- ADDRESS in Excel is a Lookup and Reference function used to retrieve the cell text address based on specified row and column numbers, commonly used alongside the CELL function for obtaining various cell information.
- The syntax is =ADDRESS(row_num, column_num, abs_num, a1, sheet_text), where row_num and column_num denote the row and column numbers, abs_num specifies the reference type, a1 defines the reference style, and sheet_text indicates the worksheet name.
- Access the function via the Formulas tab or directly by typing it in a cell. Alternatively, use the Insert function button in the Formulas tab for manual input.
Cell ADDRESS Function Formula
The function’s syntax is:
= ADDRESS (row_num; column_num; abs_num; a1; sheet_text)
The function uses the following arguments:
- row_num: is the row number (where one stands for row number 1, two for row number 2, etc.)
- column_num: is the column number (where one stands for column A, two for column B, etc.)
- abs_num: is one of four reference types:
- 1 or omitted – returns an absolute reference type
- 2 – returns an absolute row/relative column reference type
- 3 – returns a relative row/absolute column reference type
- 4 – returns a relative reference type
- a1: is one of two reference styles:
- TRUE or omitted – A1 style, where columns are labeled alphabetically, and rows are labeled numerically
- FALSE – R1C1 style, where columns and rows are labeled numerically
- sheet_text: is the text that specifies the worksheet's name to be used as the external reference. If omitted, the address returned by the function will refer to a cell in a current worksheet.
How to use the ADDRESS Function in Excel?
As mentioned earlier, the function can be accessed using several methods. First, you can access it through the Formulas tab located at the top of an Excel worksheet.
Second, you can select any cell in a worksheet and start typing the function in:
Finally, you can start with the Formulas tab and continue with the manual input of the function. If you prefer this method, simply click the Formulas tab, select the Insert function button, and proceed with typing the syntax.
Note
While the row and column numbers are required arguments, all other arguments are optional.
The required arguments can either be inputted manually or with the help of other Excel functions, such as ROW and COLUMN functions.
The ROW function returns the row number of the argument/reference. On the other hand, the COLUMN function returns the column number of the argument/reference.
Cell ADDRESS Function Example
The table below provides examples of the direct use of the function.
More specifically:
- row 2 demonstrates the use of the function to get an absolute cell reference
- row 3 demonstrates the use of the function to get a relative column/absolute row cell reference
- row 4 demonstrates the use of the function to get an absolute column/relative row cell reference
- row 5 demonstrates the use of the function to get a relative cell reference
- row 6 demonstrates the use of the function to get an absolute cell reference in the R1C1 style
- row 7 demonstrates the use of the function to get a relative cell reference in the R1C1 style
- row 8 demonstrates the use of the function to get an absolute cell reference to another worksheet
- row 9 demonstrates the use of the function to get an absolute cell reference using the ROW and the COLUMN functions
The ADDRESS function can also be simultaneously used in combination with other functions. For example, combining the ADDRESS and the INDIRECT functions can do the job if you want to get a value from a specific cell based on the row and column numbers.
The INDIRECT function turns the text into a regular reference and returns the value from the respective cell.
Another example would be combining the ADDRESS function with the SUBSTITUTE function. It comes in handy if you want only to get a column letter. The following example provides more details.
As you can see, the SUBSTITUTE function replaced the row number with a blank.
Keep in mind that, for the most part, working with the ADDRESS function is pretty straightforward. However, there are cases when the function returns the #VALUE! error. These cases are:
- When the row numbers are either < 1 or > the number of rows in the Excel spreadsheet
- When the column numbers are either < 1 or > the number of columns in the Excel spreadsheet
- When any of the supplied arguments are non-numeric values or are not recognizable as logical values
Free Resources
To continue learning and advancing your career, check out these additional helpful WSO resources:
or Want to Sign up with your social account?