XLOOKUP and Dynamic Ranges

[ChrisNeilsen]

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)

[#2]

@ChrisNeilsen 

for the return argument you can wrap the column into a SortBy function.

=XLOOKUP(G4,SORT(Table1[foo]),SORTBY(Table1[bar],Table1[foo],1),0,2)

[#3]

@ChrisNeilsen 

As a comment

https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376

[#4]

@ChrisNeilsen Hi! How can i use XLOOKUP in russian version of Excel?

[#5]

@Bulatmus 

I don't think it's localised right now. But it will be interesting to know how Microsoft call this function in Russian. VLOOKUP is ВПР, HLOOKUP is ГПР. Perhaps XLOOKUP will be ВСЁПР, who knows. By the way, what the ПР is?

[#6]

@Ingeborg Hawighorst 

While wrapping the ranges in Sort / SortBy "works" (in that it returns the correct result) it is extreamly slow, to the point of being unusable.  I did a small test case, using a Table1 of 10,000 rows, 2 columns, random order. I then did 10,000 lookups (using three different methods).  These tests are based on the premise that the Table the lookups are referencing remains static (well, changes rarely)

Test 1

=XLOOKUP(H2,Table1[Foo],Table1[Bar],0,1)

On my hardware, approx 50mS per lookup

Test 2

Added 2 more columns 

=SORT(Table1[Foo]) ' Named first cell FooSorted
=SORTBY(Table1[Bar],Table1[Foo]) ' Named first cell BarSorted

Tested formula

=XLOOKUP(H2,FooSorted#,BarSorted#,0,2)

approx 30mS per lookup 

Test 3

=XLOOKUP(H2,SORT(Table1[Foo]),SORTBY(Table1[Bar],Table1[Foo],1),0,2)

approx 200,000mS per lookup

Conclusion:

1. Imbedding Sort into the XLookup is, well, not a good idea...
2. Referencing a sorted list does have benifit, and enough of a benifit to be worthwhile when using a large number of lookups
3. I stumbled on a workable solution to my question: don't use just =Sort(Table1), instead use =Sort(Table1[ColumnToSortOn]) and a seperate =SortBy((Table1[Column],(Table1[ColumnToSortOn]) for each column of intereset

[#7]

@ChrisNeilsen 

Or wait GA of XLOOKUP. As noted here https://support.office.com/en-us/article/XLOOKUP-function-B7FD680E-6D10-43E6-84F9-88EAE8BF5929 XLOOKUP is in beta stage and will be optimized in coming months.