XLookup is a great addition to Excel. Looks particularly promising when combined with Dynamic Arrays.
There is one scenario I'm not sure has been catered for:
Lets say we have a Table with several columns, We want to look up a value in Column1, and return the corresponding value from Column2.
Simple enough:
| =XLOOKUP(A1,Table1[Column1],Table1[Column2],0,1) |
Now, lets say it's a large table (tens of thousands or rows) and we want to do many (thousands) lookups. So we want to take advantage of the new Binary Search capability of XLookup: So we add a sorted Dynamic Range formula =SORT(Table1) and want to so the lookup into that instead (assume named range Table1Sorted refers to the sorted spill range eg =Sheet1!$Q$4#)
In pseudo code, we want
| =XLOOKUP(A1,Table1Sorted[Column1],Table1Sorted[Column2],0,2) |
So, how do we refer to the Spill Range columns? Idealy we would use Structured References as in the pseudo code, but that doesn't work.
(One work around would be to add some INDEX's, but to my eye that's quite cumbersome, is not structured, and may take away from the efficiency)
| =XLOOKUP(A1,INDEX(Table1Sorted,,1),INDEX(Table1Sorted,,2),0,2) |
