Forum Discussion

Pooja Pandey's avatar
Pooja Pandey
Copper Contributor
May 04, 2018

Huge Excel spreadsheet- Slow calculation speed for formulas

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!

  • Dan Bergen's avatar
    Dan Bergen
    Copper Contributor

    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. 

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    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

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

Resources