sheet slowing down

**Winoc** · 11-29-2018, 05:15 PM

Hi everyone,

I have a sheet that has to vlookup information from another sheet but it slows down my file terribly, especially in combination with macro's. Is there a trick to make it faster again? Unfortunately it isn't an option to have the data from the second sheet inserted on a separate tab of the first sheet since the people who handle the second sheet may not have access to the first one.

I hope you understand what I'm trying to say? :-)

Thanks for your help!

Winoc

**dosydos** · 11-29-2018, 05:35 PM

a few things i can think of:
1) use =Index(Match()) functions instead of vlookup.
2) when using index match or vlookup make sure you aren't selecting the entire column of data. (ex. S:S instead use S1:S5000) <-- this saves on data that must be analyzed

You want to use Index Match over vlookup for many reasons.
1) in a vlookup your criteria column must be the first column. With index match your criteria column can be ANY column that you specify.
2) When using index match your matching column can be only one column vs a vlooup you have a whole slew of columns in the array and you must indicate with a numbered value what column you are selecting.

**MrShorty** · 11-29-2018, 05:40 PM

I don't know about "tricks" to make this faster, but there are certainly some things to consider:

1) Are you using slow, linear lookups (4th argument of VLOOKUP() is 0 or FALSE)? If so, change them to much more efficient binary lookups (4th argument of VLOOKUP() is 1, TRUE, or omitted. Also note that the lookup table must also be sorted in ascending order, if it isn't already). https://support.office.com/en-us/art...8-93a18ad188a1
2) Eliminate duplicated effort. If you are doing the same lookup multiple times, use a helper column to perform the slow lookup step once, then use INDEX() functions to extract the needed information (see post #6 and post #13 here: https://www.excelforum.com/excel-for...ml#post4041181 ).
3) Consider other tools/utilities (would this be faster as a filter? or maybe a utility like Get and Transform can extract the necessary information more efficiently?).
4) Get rid of volatile functions (INDIRECT() OFFSET() are the big offenders).

Does any of that help redesign your spreadsheet?

**andrewjahns** · 11-29-2018, 06:30 PM

When my spreadsheets start getting large and slowing down, I recreate them and it typically does the trick. Odd, but it seems to work.

**Winoc** · 07-23-2019, 08:39 AM

this is very helpful, thanks! I also found a way in VBA to not show how Excel's running the macros and this helped an awful lot. I still think many of my VBA codes could be shortened but I just don't have the knowledge to do so :-)