In part 1 I looked at how FastExcel SpeedTools MEMLOOKUP and AVLOOKUP2 compared to VLOOKUP and INDEX/MATCH for ease of use and power.
This post will benchmark the performance of the SpeedTools lookups against the standard Excel functions.
Download the Test Workbooks to your system
You can download a free 30-day trial of SpeedTools from the Decision Models website.
The test workbooks are VLOOKUP2.xlsx and MEMLOOKUP2.xlsx in the downloadable file MemLookup2.zip .
The LOOKUP Dependency Problem
A problem with all Excel LOOKUP formulas is that if even if only one of the values in the Lookup Table changes every single LOOKUP formula that refers to the lookup table gets recalculated, although most of them will returns a completely unchanged answer. When you have large amounts of data (tens or hundreds of thousands of rows) this can be very slow.
Exact Match with Sorted Data
SpeedTools MEMLOOKUP and AVLOOKUP2 both use a variation of the superfast binary search algorithm that can do exact match searches on sorted data. You can make Excel’s VLOOKUP do a similar thing by using two VLOOKUPS and an IF (see Why 2 VLOOKUPS are better than 1 VLOOKUP).
If you sort the data in the test workbook and use the sorted data option it takes about 0.14 seconds to do 20000 MEMLOOKUPs on 70000 rows on my system. This compares with about 4.25 seconds to do the same thing with VLOOKUP using the VLOOKUP exact match option. (The 2 VLOOKUPS trick is faster than MEMLOOKUP but more complicated!).
Exact Match with Unsorted Data
But if your data is not sorted you are stuck with doing a slow linear search from the start until a match is found. The VLOOKUP2.xlsx file has 20000 VLOOKUPs on a lookup table with 70000 rows.
This calculates in 4.25 seconds on my desktop system (Intel i7 quad core 870 2.93GHz with 4 GB RAM and using Excel 2013 32-bit and Windows 7). This actually quite fast if you consider that Excel has to make about 1100 million comparisons (so thats 258 MXIPS – Million eXcel Instructions Per Second).
But if you do exactly the same thing (see test workbook MEMLOOKUP2.xlsx) using SpeedTools MEMLOOKUP it only takes 0.12 seconds! Thats about 35 times faster.
So how does it work?
The MEMLOOKUP and AVLOOKUP family of functions are implemented using a multi-threaded C++ XLL. This is the fastest available technology for extending Excel’s function library, and allows the functions to support all the Excel versions from Excel 2013 64-bit to Excel 2000.
Using Lookup Memory with MEMLOOKUP and AVLOOKUP2
MEMLOOKUP and AVLOOKUP2 store in memory the index of the lookup result for each row.
So suppose for the MEMLOOKUP on row 3 the result was found in the 47th row of the lookup table. Then MEMLOOKUP stores in memory 47 for row 3.
At the next recalculation of that formula MEMLOOKUP first looks in the memory, finds 47 and checks if the lookup column row 47 still gives the correct result.
If it does then MEMLOOKUP returns the result from the answer column of row 47 in the lookup table.
If row 47 no longer gives the correct result (because the data in the lookup column on that row in the lookup table has changed) then MEMLOOKUP does a full lookup.
This is a fail-safe and very efficient process.
If (as often happens) you have more than one lookup on the same row returning data from different columns then the lookup memory can be reused for the subsequent lookups. This built-in optimisation is similar to creating an extra MATCH column with several MATCH formulas referring to the MATCH, but is much simpler and more automatic.
Memory is stored with the workbook.
The lookup memory is automatically stored and retrieved with the workbook so that when you reopen a workbook your MEMLOOKUP and AVLOOKUP2 formulas will reuse the lookup memory from the previous calculation.
Memory Type Options
SpeedTools has options for 4 different kinds of lookup memory:
- Book-Sheet-Row memory (default option): This option stores the index separately for each Workbook, Worksheet and row. This works well unless you are using lookups on multiple tables within the same row on a worksheet.
- Named Memory: this option stores the index separately for each combination of Name, Workbook and row. Usually you would use the same name for the memory as the lookup table. This allows for optimising the re-use of the lookup memory across all the worksheets in a workbook for each lookup table, and for multiple lookups on different tables within a single formula.
- Global memory for rows or columns: This option stores the index globally for each row or column so that it can be re-used acroos all open workbooks and worksheets. This is the most efficient option for a single lookup table.
- Book-Sheet-Cell memory: this option provides the most tightly scoped memory.
The SpeedTools MEMLOOKUP and AVLOOKUP family of Lookup functions provide significant performance advantages compared to the standard Excel lookup functions, together with enhanced ease of use and extended capability.
Please try them out and let me know what you think.