|
High-performance exact match Memory Lookups
Written in C++, SpeedTools multi-threaded Lookup functions provide faster and easy-to-use alternatives to Excel’s MATCH and VLOOKUP functions for exact match lookups on both sorted and unsorted data.
Excel VLOOKUP does exact match lookups by starting at the first row and looking at each row in turn until a match is found. For large numbers of rows this linear search is slow.
SpeedTools Lookups remember which rows gave a match the last time they were calculated and will try to short-circuit the slow linear search by checking the remembered row first. Because Excel has to recalculate Lookups whenever any of the values in the Lookup Table change (even when the answer does not change) this technique can provide significant speedups.
For sorted data SpeedTools Lookups uses exact match binary search which is much faster than the exact match linear search you have to use with Excel lookups.
Lookup Memory is Fail-Safe
If the memory row does NOT give an exact match to the value being looked up then SpeedTools Lookups will do an exact match linear search or exact match binary search.
Four different kinds of Lookup Memory
SpeedTools Lookups can use 4 different kinds of multi-threaded memory:
Re-useable Global Memory for Rows or Columns
A single index is stored in global memory for each row (vertical lookups) or column (horizontal lookups).
Global memory is super-efficient and easy to use because it gets re-used when you have more than one lookup formula on the same row looking up different columns in a Table. This global memory works across all worksheets in all open workbooks.
Book Sheet Row Memory (default option)
If you are using different Lookup Tables on different worksheets but still want to have more than one lookup formula on a row looking up different columns from the same table you should use this option which stores the row memory separately for each workbook & worksheet.
Book Sheet Cell Memory
If you are using multiple Lookup Tables formulas on the same sheet and the same row but in different cells you can use this option which stores the memory separately for each cell.
Named Row Memory within Workbook
If you are using a number of Lookup Tables it may be better to use a separate Named Row Memory for each table. This allows you to use multiple memory lookup formulas that look up the tables in the same row or even in the same cell. The named row memory is shared by name across all worksheets within a workbook.
Lookup Memory is stored with the workbook
All four kinds of Lookup Memory can be stored with the workbook so that it is automatically restored when you next open the workbook.
Managing the Global Lookup Memory pools
The current Lookup Memory is stored each time the workbook is saved. When a workbook is opened its stored Lookup Memory is added to the global Lookup Memory pool.
The Lookup Memory Pool persists until the Excel session is closed or Lookup Memory is cleared.
The SpeedTools Settings tab in FastExcel SpeedTools Calculation Options and Settings enables you to control Lookup Memory.
.png)
You can choose whether to save the Lookup Memory and whether to always store it externally to the workbook. For Excel 2003 and earlier the Lookup Memory is always stored externally. External memory files are stored in the same directory as the workbook and have file extensions of .mrl, .mcl and .mcs
Clear Memory empties the current Lookup Memory.
Count Lookup Memory gives you a count of each of the three different kinds of Lookup Memory.
Retrieve Memory repopulates the Lookup Memory from the workbook or the external files.
Choice of simple or advanced Lookup Functions
MEMMATCH and MEMLOOKUP provide a simple way of replacing your existing exact match VLOOKUP, HLOOKUP, LOOKUP and MATCH formulas with more efficient memory-based functions.
MEMMATCH and MEMLOOKUP can accept arrays and calculated ranges for the Lookup_Table, and will do vertical or horizontal lookups depending on the shape of the Lookup Table.
There an additional 24 Advanced SpeedTools Lookup functions that give you many extended options.
|
