I think it makes sense for me to explain this, then!
In fact, this did not help me use a technique that is designed to circumvent the need to enter a formula in the form of an array formula, i.e. using CSE. Although this can be considered a plus on some accounts, I think I was mistaken to use it here, and probably will not do it anymore.
The method involves adding additional INDEX functions to the appropriate places in the formula. This forces other functions that, without entering an array, usually act only on the first element of any array passed to them, instead they work on all elements inside this array.
However, while inserting a single INDEX function to avoid CSE is, in my opinion, perfectly fine, I think when it comes to the point where you use two or three (or even more) such coercions, then you probably you should consider whether it’s worth it all (a few tests that I have done show that in many cases the performance is worse in the worst case than in the version other than INDEX than the equivalent CSE setting). Also, using array formulas is something to be encouraged, not something to be avoided.
Sorry for the speed, but actually it is, because if I gave you a version of the array, then you might not have returned for an explanation, as this version would look like this:
= INDEX (B4: B10, MATCH (TRUE, ABS (A4: A10-B1) = MIN (ABS (A4: A10-B1)), 0))
which is objectively much easier to syntactically understand than another version.
Let me know if this helps and / or you still want me to go through a breakdown of any decision that I would love to make.
You can also find the following interesting links (I hope that I do not break any rules of this site by posting them):
https://excelxor.com/2014/09/01/index-an-alternative-to-array-cse-formulas https://excelxor.com/2014/08/18/index-returning-entire-rowscolumns
Hi