xlookup with binary search

[Shane Devenshire]

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

[#2]

Hi Shane

You can't do a Binary Search with a wildcard option

[#3]

@Shane Devenshire 

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!

[#4]

Hi,

Problem solved - author's mental disconnect.

[#5]

Came to that conclusion after a little thought, thanks!

[#6]

@Peter Bartholomew 

Thanks,

Wild card makes no sense for a binary search - I just wasn't using my gray matter, what there is left of it.