Forum Discussion
Binary search with 2 search criteria
How can one do a binary search with 2 or more search criteria ? I want to do basically what VLOOKUP was for, but use two search criteria and for speed a binary search. I found how to do a binary...
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.
Is there any improvement in speed by using XLOOKUP binary search option (last parameter = 2)?
- Average calc speed with 6 timings was .000932 seconds.