Binary search with 2 search criteria

[Amoranemix]

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 ?

[#2]

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

[#3]

@Patrick2788 

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

[#4]

Average calc speed with 6 timings was .000932 seconds.

[#5]

@Patrick2788 

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.