Forum Discussion
xlookup with binary search
Hi Folks, The following formula works: =XLOOKUP("*1*",A:A,A:A,,2,1) but this one doesn't: =XLOOKUP("*1*",A:A,A:A,,2,2) The same data is being used for both and is sorted ascending on A:A. Th...
Hello Shane
Another thought is "Do not use search modes 2 or -2"; the dynamic array solutions will take care of any sorting required in order to optimise the search without the user being involved.
A further thought is "do you believe the old strategy of creating references of the form A:A is compatible with modern dynamic array philosophy (except, of course, the circumstance in which the array has precisely 1048576 terms)? I see it as a legacy approach to creating dynamic behaviour in solution that relies upon the 'used range' to function; we now have better!
Thanks,
Wild card makes no sense for a binary search - I just wasn't using my gray matter, what there is left of it.
