Binary search with 2 search criteria

New Contributor

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 search with XLOOKUP and how to use multiple search criteria in XLOOKUP, but I don't know how to combine them.

 

In other words, I have a table with three (or more) columns that can be sorted. I want a value in the third column for a given value in first column and a given value in second column and the amount of searching that has to be done is very large and since Excel often unnecessarily recalculates, that easily makes the amount of searching huge.

 

Can anyone explain how it can be done ?

3 Replies

@Amoranemix 

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. 

@Patrick2788 

 

Is there any improvement in speed by using XLOOKUP binary search option (last parameter = 2)?

Average calc speed with 6 timings was .000932 seconds.