Forum Discussion
Binary search with 2 search criteria
If I understand this request correctly, you're looking to do an efficient 2 criteria lookup,
With XLOOKUP/Binary search you'll either want to sort the source data A-Z or sort the data in-formula. Something like this:
=LET(data,SORT(A2:D5001,{1,3}),a,TAKE(data,,1),c,CHOOSECOLS(data,3),d,TAKE(data,,-1),XLOOKUP(H14:H20&I12,a&c,d,,,1))
I've attached a sample workbook with timings for VLOOKUP (exact match, unsorted), XLOOKUP (Exact, unsorted) and XLOOKUP (Sorted, binary match). The latter faired best in the timings.
My previous post apparently got lost somewhere. Thank you for the solution.
I simplified the formula to
XLOOKUP(G4&G$2; A2:A5001&C2:C5001; D2:D5001;;; 2)What I had tried before didn’t work because I had used entire columns (like A:A i.s.o. A2:A50001).
There is still one problem : Apparently the maximum length of the sublist created by the first criterion to be searched by the second criterion is 64. For example, if you are searching for street names and the street you are looking for exists more than 64 times, then the list of cities (2nd criterion) will be more than 64 long. Excel then won’t find any city that appears too late in the 2nd list. Can that be solved and if so, how?
@brunomerola :
If you presort your table i.s.o. doing the sorting in your formula, it should be faster and much faster for long lists.
Is there any improvement in speed by using XLOOKUP binary search option (last parameter = 2)?
- Average calc speed with 6 timings was .000932 seconds.
