Home

XLOOKUP and Dynamic Ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-847695%22%20slang%3D%22en-US%22%3EXLOOKUP%20and%20Dynamic%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847695%22%20slang%3D%22en-US%22%3E%3CP%3EXLookup%20is%20a%20great%20addition%20to%20Excel.%26nbsp%3B%20Looks%20particularly%20promising%20when%20combined%20with%20Dynamic%20Arrays.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20one%20scenario%20I'm%20not%20sure%20has%20been%20catered%20for%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELets%20say%20we%20have%20a%20Table%20with%20several%20columns%2C%26nbsp%3B%20We%20want%20to%20look%20up%20a%20value%20in%20Column1%2C%20and%20return%20the%20corresponding%20value%20from%20Column2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimple%20enough%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3DXLOOKUP(A1%2CTable1%5BColumn1%5D%2CTable1%5BColumn2%5D%2C0%2C1)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20lets%20say%20it's%20a%20large%20table%20(tens%20of%20thousands%20or%20rows)%20and%20we%20want%20to%20do%20many%20(thousands)%20lookups.%26nbsp%3B%20So%20we%20want%20to%20take%20advantage%20of%20the%20new%20Binary%20Search%20capability%20of%20XLookup%3A%26nbsp%3B%20So%20we%20add%20a%20sorted%20Dynamic%20Range%20formula%26nbsp%3B%26nbsp%3B%3CSTRONG%3E%3DSORT(Table1)%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eand%20want%20to%20so%20the%20lookup%20into%20that%20instead%20(assume%20named%20range%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ETable1Sorted%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Erefers%20to%20the%20sorted%20spill%20range%20eg%26nbsp%3B%3DSheet1!%24Q%244%23)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20pseudo%20code%2C%20we%20want%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3DXLOOKUP(A1%2CTable1Sorted%5BColumn1%5D%2CTable1Sorted%5BColumn2%5D%2C0%2C2)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20how%20do%20we%20refer%20to%20the%20Spill%20Range%20columns%3F%26nbsp%3B%20Idealy%20we%20would%20use%20Structured%20References%20as%20in%20the%20pseudo%20code%2C%20but%20that%20doesn't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(One%20work%20around%20would%20be%20to%20add%20some%20INDEX's%2C%20but%20to%20my%20eye%20that's%20quite%20cumbersome%2C%20is%20not%20structured%2C%20and%20may%20take%20away%20from%20the%20efficiency)%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3DXLOOKUP(A1%2CINDEX(Table1Sorted%2C%2C1)%2CINDEX(Table1Sorted%2C%2C2)%2C0%2C2)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-847695%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847952%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20and%20Dynamic%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847952%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406284%22%20target%3D%22_blank%22%3E%40ChrisNeilsen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Efor%20the%20return%20argument%20you%20can%20wrap%20the%20column%20into%20a%20SortBy%20function.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DXLOOKUP(G4%2CSORT(Table1%5Bfoo%5D)%2CSORTBY(Table1%5Bbar%5D%2CTable1%5Bfoo%5D%2C1)%2C0%2C2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20880px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131067iE3D0422A0365771D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-09-11_14-27-06.png%22%20title%3D%222019-09-11_14-27-06.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853423%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20and%20Dynamic%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853423%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406284%22%20target%3D%22_blank%22%3E%40ChrisNeilsen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20comment%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131584iF83176DF735D2313%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FAnnouncing-XLOOKUP%2Fba-p%2F811376%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FAnnouncing-XLOOKUP%2Fba-p%2F811376%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857191%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20and%20Dynamic%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406284%22%20target%3D%22_blank%22%3E%40ChrisNeilsen%3C%2FA%3E%26nbsp%3BHi!%20How%20can%20i%20use%20XLOOKUP%20in%20russian%20version%20of%20Excel%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857594%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20and%20Dynamic%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409614%22%20target%3D%22_blank%22%3E%40Bulatmus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20think%20it's%20localised%20right%20now.%20But%20it%20will%20be%20interesting%20to%20know%20how%20Microsoft%20call%20this%20function%20in%20Russian.%20VLOOKUP%20is%20%D0%92%D0%9F%D0%A0%2C%20HLOOKUP%20is%20%D0%93%D0%9F%D0%A0.%20Perhaps%20XLOOKUP%20will%20be%20%D0%92%D0%A1%D0%81%D0%9F%D0%A0%2C%20who%20knows.%20By%20the%20way%2C%20what%20the%20%D0%9F%D0%A0%20is%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857941%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20and%20Dynamic%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857941%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20wrapping%20the%20ranges%20in%20Sort%20%2F%20SortBy%20%22works%22%20(in%20that%20it%20returns%20the%20correct%20result)%20it%20is%20extreamly%20slow%2C%20to%20the%20point%20of%20being%20unusable.%26nbsp%3B%20I%20did%20a%20small%20test%20case%2C%20using%20a%20Table1%20of%2010%2C000%20rows%2C%202%20columns%2C%20random%20order.%20I%20then%20did%2010%2C000%20lookups%20(using%20three%20different%20methods).%26nbsp%3B%20These%20tests%20are%20based%20on%20the%20premise%20that%20the%20Table%20the%20lookups%20are%20referencing%20remains%20static%20(well%2C%20changes%20rarely)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETest%201%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DXLOOKUP(H2%2CTable1%5BFoo%5D%2CTable1%5BBar%5D%2C0%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EOn%20my%20hardware%2C%20approx%2050mS%20per%20lookup%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETest%202%3C%2FP%3E%3CP%3EAdded%202%20more%20columns%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSORT(Table1%5BFoo%5D)%20'%20Named%20first%20cell%20FooSorted%0A%3DSORTBY(Table1%5BBar%5D%2CTable1%5BFoo%5D)%20'%20Named%20first%20cell%20BarSorted%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ETested%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DXLOOKUP(H2%2CFooSorted%23%2CBarSorted%23%2C0%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eapprox%2030mS%20per%20lookup%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETest%203%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DXLOOKUP(H2%2CSORT(Table1%5BFoo%5D)%2CSORTBY(Table1%5BBar%5D%2CTable1%5BFoo%5D%2C1)%2C0%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eapprox%20200%2C000mS%20per%20lookup%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConclusion%3A%3C%2FP%3E%3COL%3E%3CLI%3EImbedding%20Sort%20into%20the%20XLookup%20is%2C%20well%2C%20not%20a%20good%20idea...%3C%2FLI%3E%3CLI%3EReferencing%20a%20sorted%20list%20does%20have%20benifit%2C%20and%20enough%20of%20a%20benifit%20to%20be%20worthwhile%20when%20using%20a%20large%20number%20of%20lookups%3C%2FLI%3E%3CLI%3EI%20stumbled%20on%20a%20workable%20solution%20to%20my%20question%3A%20don't%20use%20just%20%3DSort(Table1)%2C%20instead%20use%20%3DSort(Table1%5BColumnToSortOn%5D)%20and%20a%20seperate%20%3DSortBy((Table1%5BColumn%5D%2C(Table1%5BColumnToSortOn%5D)%20for%20each%20column%20of%20intereset%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860001%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20and%20Dynamic%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406284%22%20target%3D%22_blank%22%3E%40ChrisNeilsen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20wait%20GA%20of%20XLOOKUP.%20As%20noted%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FXLOOKUP-function-B7FD680E-6D10-43E6-84F9-88EAE8BF5929%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FXLOOKUP-function-B7FD680E-6D10-43E6-84F9-88EAE8BF5929%3C%2FA%3E%20XLOOKUP%20is%20in%20beta%20stage%20and%20will%20be%20optimized%20in%20coming%20months.%3C%2FP%3E%3C%2FLINGO-BODY%3E
ChrisNeilsen
New Contributor

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)
6 Replies

@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)

 

2019-09-11_14-27-06.png

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

@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?

@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

 

@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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies