Forum Discussion
XLOOKUP and Dynamic Ranges
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 s...
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)
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 BarSortedTested 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:
- Imbedding Sort into the XLookup is, well, not a good idea...
- Referencing a sorted list does have benifit, and enough of a benifit to be worthwhile when using a large number of lookups
- 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