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.Speed Tools has options for 4 different kinds of lookup memory: The Speed Tools 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.As you remember, the INDEX function can return the value at the juncture of a given row and column, but it cannot determine which exactly row and column you want.
For example, if the range B1: B3 contains the values "New-York", "Paris", "London", then the formula For more information about the MATCH function, please see How to use MATCH function in Excel.
At first sight, the usefulness of the MATCH function may seem questionable.
But if you do exactly the same thing (see test workbook MEMLOOKUP2.xlsx) So how does it work?
The MEMLOOKUP and AVLOOKUP family of functions are implemented using a multi-threaded C XLL.
Speed Tools 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).(The 2 VLOOKUPS trick is faster than MEMLOOKUP but more complicated! 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 VLOOKUP2file has 20000 VLOOKUPs on a lookup table with 70000 rows.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.You will find a number of formula examples that will help you easily cope with many complex tasks when VLOOKUP fails.