Excel Functions Tutorials
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- HLOOKUP Formula in Excel
- HLOOKUP Examples
- Hyperlink Excel Function
- Hyperlink Formula in Excel
- INDIRECT Function in Excel
- LOOKUP Excel Function
- LOOKUP Formula in Excel
- Match Excel Function
- MATCH Formula in Excel
- How to Match Data in Excel?
- VLOOKUP Excel Function
- VLOOKUP Formula
- VLOOKUP Tutorial in Excel
- VLookup in VBA Excel
- VLOOKUP in Pivot Table
- VLOOKUP with SUM
- VLOOKUP with Match
- SUMIF With VLOOKUP
- VLookup with IF Statement
- Vlookup to the Left
- VLOOKUP from Another Sheet / Workbook
- VLOOKUP Examples in Excel
- VLOOKUP Table Array
- VLOOKUP vs HLOOKUP
- INDEX Excel Function
- INDEX Formula in Excel
- Indirect Formula in Excel
- TRANSPOSE Excel Function
- Row Function in Excel
- Rows Function in Excel
- OFFSET Excel Function
- OFFSET Formula in Excel
- VLOOKUP with Multiple Criteria
- IFERROR with VLOOKUP in Excel
- Fixing VLOOKUP Errors
- Alternatives to Vlookup
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
Address in Excel (Table of Contents)
Address Function in Excel
The Address function in Excel lets you derive the reference excel address of cell or a range passed in the parameter of the Address function Excel. The value derived from the Address function in excel can be passed in the INDIRECT function to get the present value of the cell/ range. Below we will see the address excel formula and how the address function can be used in the Excel.
Address Excel Formula
Explanation of Address Function in Excel
Row_Num: Is the row number to use to in the cell reference: Row_num=1 for row 1.
Column_Num: Is the column number to use in the excel address of cell reference: Col_num= 2 for column B.
Abs_num: [Optional] It is the reference type. If this parameter is omitted, the default ref_type is set to 1. The Absolute number can have any of the following values depending on individuals need:
For example $A$1
|2||Relative column; absolute row
For example A$1
|3||Absolute column; relative row
For example $A1
For example A1
A1: [Optional] It is the reference style to use either A1 or R1C1. If this parameter is omitted, the default type is set to A1.
Sheet_name – [optional] It is the name of the sheet to use in the excel address of cell. If this parameter is omitted, then no sheet name is used in the excel address of cell.
How to use the Address Function in Excel?
Let’s take a few address excel examples, before using the address function in Excel workbook:
Below we are considering all possible cases which could occur while working with the ADDRESS function in Excel. Let’s go through the given use case
- The first observation from the snapshot where row= 1 & column= 4 and address function excel can be rewritten in the simplified version as Address(1,4) which gives the result as $D$1.
- The parameters Absolut number is by default set to 1 and reference type is set to 1 (i.e. true) when these parameters have not been defined explicitly. Hence, the result has a pattern of absolute address with row and column name (i.e. $D$1).
- Here, $D signifies the Absolut column (4) and $1 signifies the Absolute Row (1).
- Consider the row 5 case, here Row= 5, column= 20 & Ab_num=2 and address function excel can be rewritten in the simplified version as Address(5,20,2) which gives the result as T$5.
- The parameter reference type is by default set to 1 (i.e. true) when the parameter has been not defined explicitly. Hence, the result has a pattern of absolute address only against the row ($5) and relative column (T).
- Here, T signifies the relative column and $5 signifies the Absolute Row.
- Now consider the case row 7 from below worksheet, here we are passing all the arguments of Address function excel including the optional ones.
- Arguments passed: row=10, column=9, Ab_num=4, A1=1, Sheet_name=Example1.
- Address Function Excel can be rewritten in the simplified version as ADDRESS(10,9,4,1,”Example1”) this gives the result as Example1!I10.
- As the Absolute number parameter is set to 4 it results in Relative reference. (I10)
Till now we have seen how the reference of a cell can be derived using the Address Function in excel but what if we are interested in the value stored in the excel address of cell. How we can get the actual value through the reference. The Indirect function would help us with above questions.
Indirect Function Formula
Reference: Is the reference of an excel address of cell
Ref_type: [Optional] Is a logical value that specifies the style of reference, R1C1 -style =False; A1 -style= True or omitted.
Passing the address using Indirect Function
The below spreadsheet shows a further example in which using INDIRECT function we can get the value for the cell reference passed in the Indirect function.
Let’s go through the first observation, Address = $D$3. Rewrite the function as Indirect($D$3).
Value present in the sample data, D3 cell is Mathematics which is same as the result of the Indirect function in B3 cell.
Consider one more example where Reference Type is of style R7C5 for this we must set the Ref_type to False (0) so the address function excel can read the reference style.
Things to note about the Address Function Excel
- The ADDRESS function in excel 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.
This has been a guide to Address Excel Function. Here we discuss the Address Formula in excel and how to use Address function in Excel along with practical examples and downloadable excel templates.