I have a very large Excel workbook which includes a number of INDEX/MATCH lookups. I am running this on a 16 core AMD processor.
It runs multi threaded calculations fine when I have about 5 (large) columns of these lookups. I can see from AMD Ryzen Master that all cores are being used efficiently.
However, when I change it to run extra lookups (10 large columns rather than 5) Excel struggles to parallelize this efficiently and I can see from Ryzen Master that not all cores are being fully utilized.
I have tried with VLOOKUP and XLOOKUP and they do not parallelize any better.
As I understand it, it is easier for Excel to parallelize if I break large formulas down into smaller cells. So I tried creating a column of helper cells using "Match" only and a column of Index cells which points to these Match cells.
However, when I autofill these new INDEX cells down a very large column, it takes ages - and Excel gives me the warning that the operation I'm about to perform involves a large amount of data.
When I autofilled a larger cell with INDEX/ MATCH it did this in seconds and gave me no warning about data.
Why is this happening and can it be fixed? Also, is there any better way to help Excel parallelize with large amounts of data in a situation like this?
Bookmarks