Sep 10 2019 02:42 PM
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) |
Sep 10 2019 07:27 PM
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)
Sep 12 2019 01:27 PM
Sep 16 2019 07:51 AM
@ChrisNeilsen Hi! How can i use XLOOKUP in russian version of Excel?
Sep 16 2019 11:29 AM
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?
Sep 16 2019 03:35 PM
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:
Sep 17 2019 03:39 PM
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.