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.
The second formula returns #VALUE.
Any ideas?
TIA,
Shane
5 Replies
Hi,
Problem solved - author's mental disconnect.
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.
- Hi Shane
You can't do a Binary Search with a wildcard optionCame to that conclusion after a little thought, thanks!
