Spreadsheet: 50362d1249753006-using-variable-array-formulae-variable-sub-array-example
Sheet: Addressing Examples

 |  | Data
 |  | Row No., [NAME] | Column No., [NAME]
 |  | 5 | 3
 |  | 0100 C5 [R5,C3] [1,1] | 0200 D5 [R5,C4] [1,2] | 0300 E5 [R5,C5] [1,3] | 0400 F5 [R5,C6] [1,4] | 0500 G5 [R5,C7] [1,5] | 0600 H5 [R5,C8] [1,6] | 0700 I5 [R5,C9] [1,7] | 0800 J5 [R5,C10] [1,8]
 |  | 9000 C6 [R6,C3] [2,1] | 1000 D6 [R6,C4] [2,2] | 1100 E6 [R6,C5] [2,3] |  |  |  |  | 1600 J6 [R6,C10] [2,8]
 |  | 1700 C7 [R7,C3] [3,1] | 1800 D7 [R7,C4] [3,2] | 1900 E7 [R7,C5] [3,3] | 2000 F7 [R7,C6] [3,4] |  |  |  | 2400 J7 [R7,C10] [3,8]
 |  | 2500 C8 [R8,C3] [4,1] | 2600 D8 [R8,C4] [4,2] |  |  |  | 3000 H8 [R8,C8] 4,6] | 3100 I8 [R8,C9] [4,7] | 3200 J8 [R8,C10] [4,8]
 |  | 3300 C9 [R9,C3] [5,1] | 3400 D9 [R9,C4 [5,2] | 3500 E9 [R9,C5] [5,3] | 3600 F9 [R9,C6] [5,4] | 3700 G9 [R9,C7] [5,5] | 3800 H9 [R9,C8] [5,6] | 3900 I9 [R9,C9] [5,7] | 4000 J9 [R9,C10] [5,8]
 |  | Statement | Result(s) | Result(s) | Result(s) | Statement | Result | Statement | Result
 |  | INDEX(C5:J9,1,1) | 0100 C5 [R5,C3] [1,1] |  |  | ROW(INDEX(C5:J9,1,1)) | 5 | COLUMN(INDEX(C5:J9,1,1)) | 3
 |  | INDEX(C5:J9,5,1) | 3300 C9 [R9,C3] [5,1] |  |  | ROW(INDEX(C5:J9,5,1)) | 9 | COLUMN(INDEX(C5:J9,5,1)) | 3
 |  | INDEX(C5:J9,1,8) | 0800 J5 [R5,C10] [1,8] |  |  | ROW(INDEX(C5:J9,1,8)) | 5 | COLUMN(INDEX(C5:J9,1,8)) | 10
 |  | INDEX(C5:[NAME]) | 4000 J9 [R9,C10] [5,8] |  |  | ROW(INDEX(C5:[NAME])) | 9 | COLUMN(INDEX(C5:[NAME])) | 10
 |  | INDEX(C5:J9,3,4) | 2000 F7 [R7,C6] [3,4] |  |  | ROW(INDEX(C5:J9,3,4)) | 7 | COLUMN(INDEX(C5:J9,3,4)) | 6
 |  | Statement | Result(s) | Result(s) | Result(s) | Comment | Comment
 |  | OFFSET(C5:J9,0,0,1,1) | 0100 C5 [R5,C3] [1,1] |  |  | )
 |  | OFFSET(C5:J9,1,1,2,2) | 1000 D6 [R6,C4] [2,2] | 1100 E6 [R6,C5] [2,3] |  | ) | ) 4 cells edited together
 |  |  | 1800 D7 [R7,C4] [3,2] | 1900 E7 [R7,C5] [3,3] |  | ) | ) 4 cells edited together
 |  | OFFSET(D6:I8,-1,-1,3,3) | 0100 C5 [R5,C3] [1,1] | 0200 D5 [R5,C4] [1,2] | 0300 E5 [R5,C5] [1,3] | ) array expressions | )) 9 cells edited together
 |  |  | 9000 C6 [R6,C3] [2,1] | 1000 D6 [R6,C4] [2,2] | 1100 E6 [R6,C5] [2,3] | ) (CTRL-UpperCase-Enter) | )) 9 cells edited together
 |  |  | 1700 C7 [R7,C3] [3,1] | 1800 D7 [R7,C4] [3,2] | 1900 E7 [R7,C5] [3,3] | ) | )) 9 cells edited together
 |  | OFFSET(D7:H7,0,4,2,3) | 3000 H8 [R8,C8] 4,6] | 3100 I8 [R8,C9] [4,7] | 3200 J8 [R8,C10] [4,8] | ) | )6 cells edited together
 |  |  | 3800 H9 [R9,C8] [5,6] | 3900 I9 [R9,C9] [5,7] | 4000 J9 [R9,C10] [5,8] | ) | )6 cells edited together
 |  | OFFSET(D6:J7,3,1,1,2) | 3500 E9 [R9,C5] [5,3] | 3600 F9 [R9,C6] [5,4] |  | ) | 2 cells edited together
 | Rule:
 | Given absolute row and column number (r,c) :       INDEX  (Xp:Yq,   r-[p-1],   c-[x-1]                   )   where for X=ABC...,x=123…
 | Given absolute row and column number (r,c) :    OFFSET  (Xp:Yq,   r-[p],       c-[x]     ,  rh,  cw  )   where for X=ABC...,x=123… and rh,cw are new array row height and column depth
 |  | Label | Row | Column | Statement |  | Result
 |  | F7 | 7 | 6 | INDEX($C$5:$J$9,$D36-($C$3-1),$E36-($D$3-1)) |  | 2000 F7 [R7,C6] [3,4]
 |  |  |  |  | OFFSET($C$5:$J$9,$D36-($C$3),$E36-($D$3),1,1) |  | 2000 F7 [R7,C6] [3,4]
 |  | C5 | 5 | 3 | INDEX($C$5:$J$9,$D38-($C$3-1),$E38-($D$3-1)) |  | 0100 C5 [R5,C3] [1,1]
 |  |  |  |  | OFFSET($C$5:$J$9,$D38-($C$3),$E38-($D$3),1,1) |  | 0100 C5 [R5,C3] [1,1]
 |  | E9 | 9 | 5 | INDEX($C$5:$J$9,$D40-($C$3-1),$E40-($D$3-1)) |  | 3500 E9 [R9,C5] [5,3]
 |  |  |  |  | OFFSET($C$5:$J$9,$D40-($C$3),$E40-($D$3),1,1) |  | 3500 E9 [R9,C5] [5,3]