Forum Discussion

ChrisNeilsen's avatar
ChrisNeilsen
Copper Contributor
Sep 10, 2019

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...
excel
IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Sep 11, 2019

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)

 

ChrisNeilsen's avatar
ChrisNeilsen
Copper Contributor
Sep 16, 2019

IngeborgHawighorst 

 

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

 

Resources