Handle LOOKUPS efficiently in a data heavy sheet

[sudipballa]

Hello Experts

I have a sheet with 55k records X 20 Columns. This is basically a file managed by few teams together.

Whenever I need to update, i handle 300 to 500 records from this sheet. For some reason, i end up doing a LOOKUP and it eats up 15 minutes to look up and paste as values.

Any tips to smoothen out this:
- Use INDEX instead of LOOKUP (OR) some other workaround?

Thanks
Kishore

 

[NARAYANK991]

Hi ,

Can you upload at least a sample workbook with around a hundred records , so that we can get an idea of your data layout , and how you want the lookup to work ?

Narayan

 

[Chihiro]

Typical methods used to alleviate slow lookup...

1. Use PowerQuery - Most robust method and easy to use with good GUI.
2. Query the workbook from another workbook using one of following methods

- MS Query - Outdated GUI and somewhat cumbersome syntax for query, but will work for all typical version (2003+)
- VBA using ADODB - Need to be careful with syntax, as well as quirks that it has. I usually don't recommend this one.
- Again PowerQuery​

3. Advanced Filter - With small piece of VBA to automate.

Slow calculation/performance can be caused by one or more of various factors and hard to pinpoint without actual workbook.

Some common reasons:
1. Too many conditional formats used in the workbook
2. Unnecessary formats beyond data range - Use CTRL + END to find last used cell
3. Volatile functions - Especially when there are many dependent cells
4. Formula range - When formula is using entire column/row as lookup range.
5. Too many Add-ins - 32 bit Excel is limited in terms of RAM it can use (I believe some where around 3~4GB with latest update for 2013 & 2016, otherwise, 2GB). And this is shared among Excel instance and all the add-ins.
6. External process is taking up available memory.

 

[sudipballa]

THanks a lot Narayan.. will give this a shot