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