Forum Discussion
How to optimize a large XLOOKUP table with volatile data sources?
I'm working with a fairly large Excel workbook (around 100,000 rows) that relies heavily on XLOOKUP functions to fetch data from multiple sheets. The problem is that performance has started to slow down significantly, especially when I open or save the file, or make changes that trigger recalculations.
To envision it :
- Each lookup table ranges from 10k to 30k rows.
- Some of the source data is linked to external workbooks or updated via Power Query.
- I've disable automatic calculation for now but that's not ideal for the final users.
- I'm not using any array formulas or dynamic arrays intentionally, but the sluggishness persists.
is there a best practice for optimizing XLOOKUP performance in large datasets ? Are there alternatives (like INDEX/MATCH) or Power Query Joins) that perform better in these situations ?
3 Replies
A fully optimized XLOOKUP works with sorted data and is arranged to spill. XLOOKUP performs a linear search by default which means it starts from the top and checks each element until a match is found or it reaches the end of the array. If you want to speed up the calculations, you'd have to either sort the data in the sheet(s) or within the formula with SORT.
A binary lookup is faster because the search needn't check each element. If you search an ID that begins with 4, the lookup checks it against the middle value of the list and determines where the return would come from, etc.
from different performance testing I seen here, the new XLOOKUP out performs the older LOOKUP and INDEX functions. Power Query does perform well. But you mentioned "not using any array formulas or dynamic arrays intentionally" but the new dynamic arrays will significantly outperform an array of XLOOKUPs. so XLOOKUP(A1:A100, ....) should be better than XLOOKUP(A1, ....) and copy/fill down
You can also try adding the data to the data model.
but ultimately it is excel not a true database so if you really want/need performance then consider taking the dive into a sql database
Re:around 100,000 rows
My idea is try to run sql with database such as sqlite.
