Huge Excel spreadsheet- Slow calculation speed for formulas

[Pooja Pandey]

I have an excel spreadsheet with about 300,000 records. I am applying a Vlookup formula on the first row and then trying to copy and paste special as formulas to the remaining rows. But, the calculation tends to consume a lot of time and shows progress in multi-threads which is really slow.

Could someone suggest a way to do this to save some time.

Thank you!

[#2]

I would recommend switching from a Vlookup to an Index/Match formula. I have done this with a similar file with a ton of formulas and was able to greatly reduce the lag time. 

[#3]

Hi Pooja,

Please try to use LOOKUP instead of VLOOKUP, but you have to sort the match column of the table array in ascending order before use it, and you have to use it as the below syntax:

=IF(<lookup_value> = LOOKUP(<lookup_value>, match_column), LOOKUP(<lookup_value>, table_array), "NA")

Please see the attached file for an example.

Regards

[#4]

In addition,

In general it's worth to check Excel performance: Tips for optimizing performance obstructions, quite useful tips.

As for the VLOOKUP in it simplest form (no embedded formulas to check criteria, etc.) it takes practically no time to recalculate on 300000 records table. In attached file such simple table is generated, no problems with performance.

Perhaps something else slows recalculation, like array formulas inside.