|
MEMLOOKUP Function
The MEMLOOKUP function uses Memory Lookup for faster exact match lookup on both sorted and unsorted data. Use MEMLOOKUP to replace VLOOKUP, HLOOKUP and LOOKUP.
MEMLOOKUP always does an exact match. On sorted data MEMLOOKUP does an exact match binary search.
If no match is found MEMLOOKUP returns #N/A even with sorted data.
For a vertical range or array MEMLOOKUP looks for a value in the leftmost column of Lookup_Array and then returns a value in the row where the value was found from a column you specify.
For a horizontal range or array MEMLOOKUP looks for a value in the topmost row of Lookup_Array and then returns a value in the column where the value was found from a row you specify.
MEMLOOKUP is a multi-threaded, non-volatile non-array function.
MEMLOOKUP Syntax
MEMLOOKUP (Lookup_Value, Lookup_Array, Col_index_num, Sort_Type, MemType_Name, Vertical_Horizontal)
The first 3 parameters are required; the last 4 parameters are optional.
Lookup_Value (required)
Specifies the value to be found in the Lookup_Array. Can be a constant or a range reference or an expression returning a single value.
Lookup_Array (required)
A vertical or horizontal array of constants, or a range reference or expression, that returns a contiguous rectangular table of Lookup Values.
Result_Column (required)
For a vertical Lookup_Array the column number or label in the header row in Lookup_Array from which the matching value should be returned. The first column in Lookup_array is column 1.
For a horizontal Lookup_Array the row number or label in the header column in Lookup_Array from which the matching value should be returned. The first row in Lookup_array is row 1.
If Result_Column is a string then Lookup_Array is assumed to contain a header row/column to be searched for Result_Column. If Result_Column cannot be found the function returns an error message “#Result Column not found in Header”. If Result_Column is a number then Lookup_Array is assumed NOT to contain a header row/column.
Sort_Type (optional - default 0)
A number 1, 0 or -1 indicating the sort sequence of Lookup_Array.
-1 Sorted Descending
0 Not Sorted
1 Sorted Ascending
The default is 0: not sorted
MemType_Name (Optional, Defaults to 2)
Memory type for lookups can be 0, 1, 2 or 3 or a string that is used as the name for a memory.
3 = Global Memory for Rows or Columns A single index is stored in global memory for each row (vertical lookups) or column (horizontal lookups).
This global memory is super-efficient and easy to use 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.
2 = Book Sheet Row Memory 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 in the same table you can use this option which stores the row memory separately for each Workbook worksheet.
1 = Book Sheet Cell Memory If you are using multiple Lookup Tables formulas in different cells on the same sheet and the same row you should use this option which stores the memory separately for each cell.
0 = Do not use Lookup memory
Named Memory If you give a text string instead of a number it will be used as a named memory. You can have many named memories in use at the same time (usually one for each Lookup Table). Each named memory works by row across all sheets within a workbook, so is the best choice when you have:
oMore than one memory lookup referring to different lookup tables within a single formula.
oMore than one memory lookup referring to different lookup tables in different cells on the same row, and these lookups are repeated on the same row in multiple sheets.
If this option is not zero, then MEMLOOKUP will first check to see if the index stored in memory that gave the answer the last time the MEMLOOKUP was calculated still gives the correct answer. If it does then MEMLOOKUP will return that answer without doing any more processing.
When using Lookup memory and there are multiple exact match answers available MEMLOOKUP will not necessarily return the answer from the same row as VLOOKUP.
Vertical_Horizontal (Optional, Defaults to 1)
This parameter controls whether the lookup is done vertically or horizontally.
0 = Guess – if the number of rows >= the number of columns vertical, otherwise horizontal
1= Always Vertical (Default)
2= Always Horizontal
MEMLOOKUP Performance
If your data is unsorted and there is no Lookup Memory already available for the position of the MEMLOOKUP formula then MEMLOOKUP will do a linear search and store the row or column index found in the Lookup Memory for subsequent MEMLOOKUP calls.
In this case the second execution of the MEMLOOKUP formula will be much faster than the first.
If your data is sorted then MEMLOOKUP will do an exact match Binary Search, which is very fast, and the second execution of the MEMLOOKUP formula will also be very fast.
|
