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:
will sum the entries in cells A1, A4 and A8.
An alternative syntax (and again, non-array) is:
We can extend this to two-dimensional ranges also:
is equivalent to:
Furthermore, we can employ the standard technique of transposing one of the array constants to generate more varied results:
giving precisely the same result as:
or even generate arrays equivalent to those for which we might normally adopt OFFSET:
giving the same result as:
which naturally is also equivalent to:
Of course, in all of these examples my choice of SUM as the coercing function was purely arbitrary, and in 99% of cases this combination of SUM and INDEX can be achieved by much more straightforward means (e.g. with SUMIF(S) or an array version of SUM).
However, we are not by any means restricted to passing the resulting array to SUM, and I have found many practical and interesting applications of this ability to generate more than one return using INDEX. Unlike OFFSET, for example, for which the first parameter must be a reference to an actual range in the worksheet, INDEX can also accept – and manipulate – for its reference arrays which consist of values generated e.g. via other subfunctions within the formula.
And the use of this technique of coercing more than one return is not restricted to INDEX. For example, attempting to find the maximum value in K1:K10 where the corresponding row entry in J1:J10 is the first occurrence in that range of either "A", "B" or "C", you might be disappointed upon seeing that the array formula:
does not always give the desired result, the reason being (and you can see this if you go through it with Evaluate Formula) that the formula resolves, undesirably, to:
i.e. only the first element in the array of three is processed by VLOOKUP.
However, using the coercion outlined above, the non-array:
does the job nicely (also note the necessary use of T() here, and not N()).
I leave it to the reader to discover the many other possibilities yielded by this technique, both using INDEX and other functions.
If what you want refuses to be coerced by array-entry alone, try this method and you might just be in luck!