excel - Use of INDEX MATCH to find absolute closest value

Question

I've long sought a method for using INDEX MATCH in Excel to return the absolute closest number in an array without reorganizing my data (since MATCH requires lookup_array to be in descending order to find the closest value greater than lookup_value, but ascending order to find the closest value less than lookup_value).

I found the answer in this post. XOR LX's solution:

=INDEX(B4:B10,MATCH(TRUE,INDEX(ABS(A4:A10-B1)=MIN(INDEX(ABS(A4:A10-B1),,)),,),0))

worked perfectly for me, but I don't know why. I can rationalize most of it but I can't figure out this part

INDEX(ABS(A4:A10-B1)=MIN(INDEX(ABS(A4:A10-B1),,))

Can anyone please explain this part?

Answer 1

I guess it makes sense for me to explain it, then!

Actually, it didn't help that I was employing a technique which is designed to circumvent having to enter a formula as an array formula, i.e. with CSE. Although that could be considered a plus by some accounts, I think I was wrong to employ it here, and probably wouldn't do so again.

The technique involves inserting extra INDEX functions at appropriate places within the formula. This forces the other functions, which without array-entry would normally act upon only the first element of any array passed to them, to instead operate over all elements within that array.

However, whilst inserting a single INDEX function for the purpose of avoiding CSE is, in my opinion, perfectly fine, I think when it gets to the point where you're using two or three (or even more) such coercions, then you should probably re-think whether it's worth it all (the few tests that I've done suggest that, in many cases, performance is actually worse off in the non-array, INDEX-heavy version than the equivalent CSE set-up). Besides, the use of array formulas is something to be encouraged, not something to be avoided.

Sorry for the ramble, but it's kind of to the point actually since, if I had given you the array version, then you may well not have come back looking for an explanation, since that version would look like:

=INDEX(B4:B10,MATCH(TRUE,ABS(A4:A10-B1)=MIN(ABS(A4:A10-B1)),0))

which is objectively far easier syntactically to understand than the other version.

Let me know if that helps and/or you still want me to go through a breakdown of either solution, which I'd be happy to do.

You may also find the following links of interest (I hope that I'm not breaking any of this site's rules by posting these):

https://excelxor.com/2014/09/01/index-an-alternative-to-array-cse-formulas https://excelxor.com/2014/08/18/index-returning-entire-rowscolumns

Regards