The property of INDEX of being able to return entire rows/columns has several important applications, one of which is to force an array of returns to be passed to another function which otherwise would require entering as an array formula, i.e. with CSE.
For example, the following formula, one possibility for returning the relative position of the first non-blank cell in the range A1:A10:
requires array-entry. Using INDEX we can produce the same effects though without need for CSE:
where, by omitting the row and column parameters, we coerce INDEX to return the entire “column” (see here for more on this property of INDEX). I should stress that the “column” here is not the worksheet range A1:A10; rather the single-“column” array consisting of the ten Boolean responses to the test condition (<>""), e.g.:
Since the INDEX here has both parameters omitted, the entire array is simply returned:
which is of course equal to 3.
This technique can generally be employed to produce a non-array version of a given array formula. Another example:
Before readers become too excited at the thought of never having to bother with CSE again, I should stress that it will not always be possible to use INDEX in this way as a substitution for array-entry.
More importantly, however, apart perhaps from not having to make the required keystroke combination for CSE, the INDEX, non-array version should not in any way be considered “better” than the corresponding array set-up: I personally went through a phase a year or so ago in which I was borderline obsessed with avoiding array-entry, so much so that I would sometimes insert up to two or three INDEX functions into a long formula to produce the necessary coercions. The end result? The formulas ended up being longer and, more tellingly, far more resource-heavy, so much so in one particular case that it forced Excel to crash on me (though the equivalent array version seemed ok!).
My advice would therefore be to by all means employ this technique as an alternative to CSE: although there is nothing particularly “wrong” with array formulas (examples of people speaking of the evil of array formulas and the necessity to avoid them at all costs abound on the internet – on enquiring as to why one particular person whom I was helping on a forum had insisted on a non-array solution, I was slightly taken aback when they replied saying they had heard that array formulas can “damage your computer”!) there is also no great harm in adding a single INDEX to a formula. What’s more, even amongst experienced users, not having to always remember to commit with CSE can be a nice thing.
Just don’t overdo it, that’s all!