I once exchanged email with an experienced Excel jockey who insisted that VLOOKUP was Excel’s fastest lookup method. It was even faster, he said, than INDEX-MATCH. To support this surprising conclusion, he sent me his testing macro.
The macro was cleverly written, but had two fatal flaws. First, his testing method had so much overhead that it overshadowed the calculation times he was trying to test. Second, the overhead varied between calculation methods.
This is why his conclusions were incorrect.
In this post, I’m going to show you my macro that tests calculation times for alternate report designs. The macro is simple, but it works.
The next post (A Volatile Workbook to Test Calculation Times for Excel Lookup Methods) will explain how to make sure your reports actually calculate when you run these tests. This is an important consideration, because if you don’t handle this step correctly, your workbook won’t fully calculate and your tests could return incorrect results.
Finally, the third post (Excel’s Fastest Lookup Methods: The Tested Results) will summarize what I learned about actual calculation times for VLOOKUP and INDEX-MATCH. You’ll see that the fastest method requires that you use both the correct lookup function(s) and the correct lookup design.
However, please keep in mind that relative calculation times shouldn’t always determine your spreadsheet design. A slow-and-easy design could be a better choice than a fast-but-difficult one.
Even so, when you make these trade-offs, it helps to know their relative calculation times. And that’s what this macro can give you.
The Calculation-Timer Macro
Here’s the control area for the calculation timer. You can see that I’ve set up two range names.
Loops specifies the number of times you want the macro to loop through the calculation process.
Results contains the total number of seconds that all calculations in the loop took.
When I set up a new calc-time test, I’ll usually run the first test with two loops. Once I see how long they take to calculate, I can estimate how many loops I actually need. On the other hand, if I enter a large number at first, I wouldn’t know whether I would need to wait five minutes or five hours for Excel to complete the test.
Here’s the CalcTimer macro:
This macro is really simple. It just loads the StartTime variable with the number of seconds that have elapsed since midnight. It calculates Excel for the specified number of times. Then it subtracts the starting time from the current time to calculate the calculation time.
Finally, it writes the calculation time to your worksheet.
In the next post, I’ll explain how I set up the data and worksheets to test the lookup methods.
