excel - Use of INDEX MATCH to find absolute closest value -

i've long sought method using index match in excel return absolute closest number in array without reorganizing data (since match requires lookup_array in descending order find closest value greater lookup_value, ascending order find closest value less lookup_value).

i found 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 me, don't know why. can rationalize of can't figure out part

index(abs(a4:a10-b1)=min(index(abs(a4:a10-b1),,))

can explain part? hate blinding using , know it'll useful in future.

i guess makes sense me explain it, then!

actually, didn't employing technique designed circumvent having enter formula array formula, i.e. cse. although considered plus accounts, think wrong employ here, , wouldn't again.

the technique involves inserting index functions @ appropriate places within formula. forces other functions, without array-entry act upon first element of array passed them, instead operate on elements within array.

however, whilst inserting single index function purpose of avoiding cse is, in opinion, fine, think when gets point you're using 2 or 3 (or more) such coercions, should re-think whether it's worth (the few tests i've done suggest that, in many cases, performance worse off in non-array, index-heavy version equivalent cse set-up). besides, use of array formulas encouraged, not avoided.

sorry ramble, it's kind of point since, if had given array version, may not have come looking explanation, since version like:

=index(b4:b10,match(true,abs(a4:a10-b1)=min(abs(a4:a10-b1)),0))

which objectively far easier syntactically understand other version.

let me know if helps and/or still want me go through breakdown of either solution, i'd happy do.

you may find following links of interest (i hope i'm not breaking of site's rules posting these):

http://excelxor.com/2014/08/23/index-an-alternative-to-array-cse-formulas/ http://excelxor.com/2014/08/23/index-returning-entire-rowscolumns/

regards