How to use Excel address function(7 examples, with Indirect,Sum,OffSet,Match and extract data)
The Address function returns the address of a cell specified by the row and column number in Excel, it can specify the type of reference, reference style, and worksheet to be referenced. There are four types of references, namely: absolute reference to column and row, absolute references to column and relative reference to row, relative reference to column and absolute reference to row, and relative reference to column and row.
Since the Address function returns the address of the cell in text mode, the result that is returned by it also needs to use the Indirect function to convert the text to a reference to the cell; then combine with OffSet, Match, Sum, Row, and Column to achieve the specific function. For example, Indirect + Address + Row + Column implements extracting data starting from the specified row, OffSet + Indirect + Address + Match can lookup data, Sum + OffSet + Indirect + Address can sum to multitable.
I, The syntax of the Excel Address function
1. Expression: ADDRESS(Row_Num,Column_Num,[Abs_Num],[A1],[Sheet_Text])
2. Description:
A. Abs_Num is optional. If omitted or 1, it returns "absolute value"; if it is 2, it returns "absolute row number, relative column label"; if it is 3, it returns "relative row number, absolute column label"; if it is 4, it returns "relative value".
B. A1 is optional. If omitted or True, returns A1style reference; if False, returns an R1C1style reference, R is the row number, and C is the column number.
C. Sheet_Text is optional. If omitted, it means to reference the current worksheet; if you want to refer to the external worksheet, just write the worksheet name, such as Sheet2.
II, The examples of Excel Address function
(I) Omitting optional arguments
1. Doubleclick cell E2, copy the formula =ADDRESS(2,3) to E2, press Enter, return the reference result to the cell as $C$2; the operation procedure is as shown in Figure 1:
Figure 1
2. Formula description:
2 is the row number, indicating the 2nd row; 3 is the column number, indicating the 3rd column in formula=ADDRESS(2,3); the formula omits the last three arguments, Abs_Num(type of reference) is the third argument of the formula, if omitted, return the absolute value, that is, the absolute reference to the cell, such as returning the result $C$2; A1(reference style) is the fourth argument of the formula, if omitted, returns the A1style reference; Sheet_Text(the name of the worksheet) is fifth argument of the formula, if omitted, returns to the current worksheet; the formula means: return an absolute reference to the cell at the intersection of the second row and the third column of the current worksheet in text, and return it with the A1style reference.
(II) Return four types of reference
1. Doubleclick cell A11, copy the formula =ADDRESS(7,4,1) to A11, press Enter, return to $D$7; doubleclick B11, paste the same formula to B11, change 1 to 2 press Enter , return D$7; doubleclick C11, copy the same formula to C11, change 1 to 3, press Enter, return to $D7; doubleclick D11, copy the same formula to D11, change 1 to 4, press Enter, return to D7; the operation steps are as shown in Figure 2:
Figure 2
2. Formula description:
A. The formula =ADDRESS(7,4,1) means: return the reference to cell at the intersection of the 7th row and the 4th column with text, and return with "absolute reference", that is, return $D$7, $ means absolute reference, there are $ before column and row, indicating absolute reference to the column and row, that is, when dragging down or to the right, D7 will not become D8, D9, ..., will not become E7, F7,....
B. The third argument "type of reference" of formula =ADDRESS(7,4,2) is 2, which means "absolute row, relative column(ie absolute reference to row, relative reference to column)" and returning a reference to a cell with text, returns D$7. When dragging down, D7 does not change to D8, D9, ..., but when dragged to the right, D7 becomes E7, F7, ....
C. The third argument "type of reference" of formula =ADDRESS(7,4,3) is 3, which means "relative row, absolute column (ie, relative reference to row, absolute reference to column)" and returns a reference to the cell with text, which returns $D7. When dragged down, D7 will change to D8, D9, ..., but when dragged to the right, D7 will not become E7, F7, ....
D. The third argument "type of reference" of formula =ADDRESS(7,4,5) is 4, indicating that "relative"(that is, relative to the row and column)" and return a reference to the cell in text, that is, return D7, when dragging down, D7 will become D8, D9, ..., when dragging to the right, D7 will also become E7, F7, ....
(III) Examples of A1style and R1C1style reference
1. Doubleclick cell E2, copy the formula =ADDRESS(6,1,,1) to E2, press Enter, return $A$6; doubleclick E3, copy the same formula to E3, and change the following 1 to 0. press Enter, return to R6C1; the operation steps are as shown in Figure 3:
Figure 3
2. Formula description:
A. The formula =ADDRESS(6,1,1,1) is to return a reference to the cell at the intersection of 6th row and 1st column in text, the third argument is omitted, the default is returned by absolute reference, the fourth argument is 1(ie True), returned in A1style, so return $A$6.
B. The formula =ADDRESS(6,1,,0) return also a reference to the cell at the intersection of 6th row and 1st column in the absolute reference and the text, the fourth argument is 0(ie False), represents returning in R1C1style, so return R6C1.
(IV) Reference to the cell of an external worksheet
1. Reference another worksheet in the same document; the current sheet is "September", if you want to reference to D5 of another sheet "October"; doubleclick E2, and copy the formula =ADDRESS(5,4,,,"October") to E2, press Enter, return October!$D$5.
2. Reference one of the worksheets of the different documents; if you want to reference to D5 in the "Sales" in the "Clothes" document; doubleclick cell E3, and copy the formula =ADDRESS(5,4,,,"[clothes.xlsx]sales") to E3, press Enter, return to [clothes.xlsx]sales!$D$5; operation process steps, as shown in Figure 4:
Figure 4
3. Formula description:
A. Formula =ADDRESS(5,4,,,"October") returns an absolute reference to D5 of another sheet "October" in the sheet "September" with text, since the two sheets are in the same document, Therefore, there is no need to add a document name in front of the sheet.
B. Formula =ADDRESS(5,4,,,"[clothes.xlsx]sales") is the absolute reference to D5 of the "Sales" in another document(ie "clothes.xlsx"). Since the document in which the formula is located is not the same document as the document in which contains the cell to be referenced, the "name of reference" must have the name of document and the name of worksheet and the name of document must have an extension, otherwise the document cannot be found, as in the formula "[clothes.xlsx]sales"; if you want to view the value in the cell to be referenced, you need to add the Indirect function, then the formula becomes =INDIRECT(ADDRESS(5,4,,,"[clothes.xlsx]sales")); In addition, the referenced document is opened and the formula returns the correct value, otherwise the reference error #REF! is returned.
III, The application examples of Excel Address function
(I) Indirect + Address + Row + Column combination extracts data starting from the specified row
1. If you want to extract data of arbitrary row from the third row. Doubleclick cell A9, copy the formula =INDIRECT(ADDRESS(ROW($A3),COLUMN())) to A9, press Enter, and return to "Cherry"; select A9 and move the mouse to the cell fill handle in the lower right corner of A9, after the mouse changes to bold black plus sign, hold down the left button, drag to the right, drag to D9, then extract the data of third row in the table; then drag down, how many rows, how many data of rows are extracted, such as drag four rows down, then extract the four data of rows in the table; the operation steps are as shown in Figure 5:
Figure 5
2. The formula =INDIRECT(ADDRESS(ROW($A3),COLUMN())) description:
A. $A3 means that the absolute reference to the column and relative to the row; when dragging down, A3 will change to A4, A5, etc. When dragging to the right, A3 will not change to B3, C3, etc.; The line number grows and the column number does not change.
B. ROW($A3) returns the row number 3 of A3, COLUMN() returns the column number 1 of the column in which contains the formula, then ADDRESS(ROW ($A3),COLUMN()) becomes ADDRESS(3,1), which returns the absolute reference to the cell at the intersection of third row and first column in text, which is "$A$3".
C. The formula becomes =INDIRECT("$A$3"), and finally returns a reference to the text "$A$3" by the Indirect function, which returns the content in A3.
D. When the formula is in B10, $A3 becomes $A4, ROW($A4) returns A4's row number 4; COLUMN() returns B10's column number 2; then the formula becomes =INDIRECT(ADDRESS(4,2)) , further calculation becomes =INDIRECT("$B$4"), and finally returns the content "Aggregate Fruit" of B4.
(II) OffSet + Indirect + Address + Match combination to lookup data(Excel address match)
1. If you want to find a Position by First Name. Doubleclick the cell B12, copy the formula =OFFSET(INDIRECT(ADDRESS(MATCH(A12,A1:A9,),1)),,2) to B12, press Enter, return to Jitter's position "Employee"; doubleclick A12, Change Jitter to Fulinter, and press Enter, then return to Fulinter's position as "Manager"; the process steps are as shown in Figure 6:
Figure 6
2. The formula =OFFSET(INDIRECT(ADDRESS(MATCH(A12,A1:A9,),1)),,2) description:
A. MATCH(A12,A1:A9,) is used to find A12 in A1:A9. It omits the last argument, the default is 0, which means exact match. The final result is 6.
B. Then ADDRESS(MATCH(A12,A1:A9,),1) becomes ADDRESS(6,1), since the latter three arguments are omitted, they all take the default value, so return the address of cell at the intersection of the sixth row and the first column with absolute reference, which returns "$A$6".
C. Since the Address function returns text, the Indirect function is also used to convert the text to a reference, ie INDIRECT("$A$6") returns $A$6.
D. The formula becomes =OFFSET($A$6,,2), and then, based on A6, returns the reference to the cell to the right of A6 with two columns, which returns a reference to C6, that is, the content "Employee" in C6 is returned. The OffSet function omits the second argument row number, the default value is 0; the fourth and fifth arguments height and width are also omitted, the height and width are the same as A6 by default.
(III) Sum + OffSet + Indirect + Address combination to sum multitable
1. If you want to subtotal the sales of clothing from September to November, the sales volume of each month is in a sheet, and the name of the sheet is named after the month. The current sheet is "September", select the "Sales subtotal" sheet, doubleclick cell B2, and copy the formula =SUM(OFFSET(INDIRECT(ADDRESS(1,4,,,A2)),1,,6)) to B2, press Enter, return to the summation result 3937; select B2, and then doubleclick the cell fill handle of B2 to return the sum of sales in October and November; the operation steps, as shown in Figure 7:
Figure 7
2. The formula =SUM(OFFSET(INDIRECT(ADDRESS(1,4,,,A2)),1,,6)) description:
A. The month in A2 is 10, then ADDRESS(1,4,,,A2) becomes ADDRESS(1,4,,,"10"), and then returns to the address of the cell at the intersection of the first row and the fourth column in the sheet "September" in absolute reference, that is, returns the text "'9'!$D$1".
B. Next, converts the text "'9'!$D$1" to a reference to the cell with the Indirect function, ie INDIRECT("'9'!$D$1"), which returns '9'!$D$1.
C. The formula becomes =SUM(OFFSET('9'!$D$1,1,,6)), further calculation, based on the D1 in the sheet "9", return the reference to cells row 1 under D1 and column 0 to right of D1 with height to 6 and width to 1, ie returns $D$2:$D$7.
D. The formula is further changed to =SUM('9'!$D$2:$D$7), and finally adds the values in D2:D7.

Related Reading
 How to move rows,columns,cells,table in excel(there
 Excel CountA and CountBlank function usage examples(
 8 examples of Excel Match function, include it and S
 How to use offset function in excel, include it and
 Excel SumProduct function(multiple criteria, with if
 How to use Excel small function and it and index, ma
 Excel left function usage(8 examples, with Sum+Value
 How to use Excel Indirect function(7 examples, with
 How to use Average function in excel(combine with if
 How to adjust row height and change width of column
 Excel SumIf function with ?/*, Average and array mul
 How to use Excel subtotal function(combine it with O