Home

xlookup with binary search

%3CLINGO-SUB%20id%3D%22lingo-sub-1054782%22%20slang%3D%22en-US%22%3Exlookup%20with%20binary%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054782%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Folks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20formula%20works%3A%3C%2FP%3E%3CP%3E%3DXLOOKUP(%22*1*%22%2CA%3AA%2CA%3AA%2C%2C2%2C1)%3C%2FP%3E%3CP%3Ebut%20this%20one%20doesn't%3A%3C%2FP%3E%3CP%3E%3DXLOOKUP(%22*1*%22%2CA%3AA%2CA%3AA%2C%2C2%2C2)%3C%2FP%3E%3CP%3EThe%20same%20data%20is%20being%20used%20for%20both%20and%20is%20sorted%20ascending%20on%20A%3AA.%3C%2FP%3E%3CP%3EThe%20second%20formula%20returns%20%23VALUE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3ETIA%2C%3C%2FP%3E%3CP%3EShane%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1054782%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055058%22%20slang%3D%22en-US%22%3ERe%3A%20xlookup%20with%20binary%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055058%22%20slang%3D%22en-US%22%3EHi%20Shane%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can't%20do%20a%20Binary%20Search%20with%20a%20wildcard%20option%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055549%22%20slang%3D%22en-US%22%3ERe%3A%20xlookup%20with%20binary%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F168784%22%20target%3D%22_blank%22%3E%40Shane%20Devenshire%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Shane%3C%2FP%3E%3CP%3EAnother%20thought%20is%20%22Do%20not%20use%20search%20modes%202%20or%20-2%22%3B%20the%20dynamic%20array%20solutions%20will%20take%20care%20of%20any%20sorting%20required%20in%20order%20to%20optimise%20the%20search%20without%20the%20user%20being%20involved.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20further%20thought%20is%20%22do%20you%20believe%20the%20old%20strategy%20of%20creating%20references%20of%20the%20form%20A%3AA%20is%20compatible%20with%20modern%20dynamic%20array%20philosophy%20(except%2C%20of%20course%2C%20the%20circumstance%20in%20which%20the%20array%20has%20precisely%201048576%20terms)%3F%26nbsp%3B%20I%20see%20it%20as%20a%20legacy%20approach%20to%20creating%20dynamic%20behaviour%20in%20solution%20that%20relies%20upon%20the%20'used%20range'%20to%20function%3B%20we%20now%20have%20better!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055684%22%20slang%3D%22en-US%22%3ERe%3A%20xlookup%20with%20binary%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055684%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EProblem%20solved%20-%20author's%20mental%20disconnect.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055689%22%20slang%3D%22en-US%22%3ERe%3A%20xlookup%20with%20binary%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055689%22%20slang%3D%22en-US%22%3E%3CP%3ECame%20to%20that%20conclusion%20after%20a%20little%20thought%2C%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055700%22%20slang%3D%22en-US%22%3ERe%3A%20xlookup%20with%20binary%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055700%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EWild%20card%20makes%20no%20sense%20for%20a%20binary%20search%20-%20I%20just%20wasn't%20using%20my%20gray%20matter%2C%20what%20there%20is%20left%20of%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Shane Devenshire
Occasional Contributor

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 Shane

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

@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!

Hi,

Problem solved - author's mental disconnect.

Came to that conclusion after a little thought, thanks!

@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.