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