Coercing array returns from CSE-resistant formulas 7

We usually face no problems in cases where we wish to apply a formula to, not just one, but an array of values. And of course we do this by simply committing the formula as an array formula, i.e. with CSE.

However, not all formulas yield so easily, and some stubbornly resist any attempts at coercing an array of returns from them. Here I would like to discuss some techniques which, in addition to array-entry, can help coerce the desired result.

The principal method in such cases is to use a construction involving OFFSET, though a set-up using INDEX is equally viable; indeed, due to its non-volatility, perhaps even preferable. Some cases may require even more coercion than that, and others less. But the one thing they all share in common is that, on its own, array-entry just isn’t enough!



We sometimes look for non-array (i.e. non-CSE) versions of constructions which would normally require array-entry. Our reasons for doing so may be varied:

1) We may feel that it improves spreadsheet performance (sometimes true, sometimes not)

2) We perhaps have a dislike for having to use the required keystroke combination necessary for committing array formulas

3) We may simply be interested from a theoretical point of view


INDEX: An alternative to array (CSE) formulas 1

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: