Perhaps one of the most widely-believed myths surrounding INDEX is that it is not possible to coerce a return of more than one value from the array passed to it.
At first sight, and after various attempts at coercing such a return (including array-entry), it does indeed seem for all the world that INDEX cannot be so induced, and stubbornly persists in returning just the first element in any array generated.
Granted, the required coercions are not obvious (the first of which is borderline tautological: the necessary initial clause, IF(1,… – or any other suitable value in place of 1 – is self-evidently TRUE), though, for example, the following non-array formula:
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:
If either (or sometimes both – see below) of the row or column parameters is zero, or omitted, and the INDEX is properly coerced (e.g. forms part of a larger formula), a reference to the entire specified column or row is generated. Note that the return here is not a single value, but rather an array of values.
We are not so much interested in returning this array in an actual cell somewhere (which in any case we can’t do), but more in being able to reference/manipulate that array in further calculations. For example: