ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
Based on The Imposing Index
http://www.excelhero.com/blog/2011/03/the-imposing-index.htm
3
4
Index returns a REFERENCE <------ Key Idea we will exploit
5
6
7
5810
8
9107inputentryformula
9
9661Basic userangenormal10
<-- get element in 1st row and 3rd column of A7:C11
10
555arraynormal99
<-- get an element from an array literal
11
6910arraynormal99
<-- get an element from an array literal (syntax variant)
12
13
2Use whole rownormal#REF!
These two versions don't work because they need to be entered as array formulas
14
normal#REF!
15
16
rangeCSE10
These work because entered as array formulas
17
rangeCSE10
18
19
So, the use of 0 or null for the row number in the above example
10
20
leads INDEX to return the entire column (hence the need for CSE).
7
21
The reason that the above example returns a single number is just
6
22
that we only entered the array formula into a single cell.
5
23
Here's the same idea used to return the entire 3rd column ----------->
10
24
25
And here's a row returned:
9107
26
Note that the trailing comma is NEEDED!
27
28
Yes the technique works with summing entire rows or columns of a two-dimensional array as well:
29
30
21
is the sum of the third row of A7:C11
31
Need to use the formula:
=SUM(INDEX(A7:C11,3,))
32
What's strange is that we DON'T need to do a CSE because of the SUM.
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100