|
MEMMATCH Function
The MEMMATCH function uses Memory Lookup for faster exact match lookup on both sorted and unsorted data. Use MEMMATCH to replace the MATCH function.
MEMMATCH always does an exact match. On sorted data MEMMATCH does an exact match binary search.
If no match is found MEMMATCH returns #N/A even with sorted data.
For a vertical range or array MEMMATCH looks for a value in the leftmost column of Lookup_Array and then returns the relative position of the item in the Lookup_Array that matches the specified value (Lookup_Value).
For a horizontal range or array MEMMATCH looks for a value in the topmost row of Lookup_Array and then returns the relative position of the item in the Lookup_Array that matches the specified value (Lookup_Value).
MEMMATCH is a multi-threaded, non-volatile, non-array function.
MEMMATCH Syntax
MEMMATCH (Lookup_Value, Lookup_Array, Col_index_num, Sort_Type, MemType_Name, Vertical_Horizontal)
The first 3 parameters are required; the last 3 parameters are optional.
Lookup_Value (required)
The value to be found in the Lookup_Array. Lookup_Value 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.
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: unsorted
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 MEMMATCH will first check to see if the index stored in memory that gave the answer the last time the MEMMATCH was calculated still gives the correct answer. If it does then MEMMATCH will return that answer without doing any more processing.
When using Lookup memory and there are multiple exact match answers available MEMMATCH will not necessarily return the answer from the same row as MATCH.
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
MEMMATCH Performance
If your data is unsorted and there is no Lookup Memory already available for the position of the MEMMATCH formula then MEMMATCH will do a linear search and store the row or column index found in the Lookup Memory for subsequent MEMMATCH calls.
In this case the second execution of the MEMMATCH formula will be much faster than the first.
If your data is sorted then MEMMATCH will do an exact match Binary Search, which is very fast, and the second execution of the MEMMATCH formula will also be very fast.
|
