Description

Returns the relative position of the specified item in a range of cells. Optimized runtime version of classic MATCH function (without dimensional cells in range).

Syntax

=MM_MATCHOPTIMIZED(Lookup_value, Lookup_array, Match_type)

• Lookup_value
  The value you want to match in the lookup_array.
  
  
• Lookup_array
  The range of cells being searched.
  
  
• Match_type (optional)
  The number -1, 0 or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

Remarks

• The difference with the default Excel function MATCH is that the MM_MATCHOPTIMIZED uses an optimized algorithm. However, this optimization could lead to a circular reference error in some specific cases due to technical reasons. Apart from this run issue, all results remain the same between MM_MATCHOPTIMIZED and MATCH.
• If at least one cell in your range Lookup_value is multi-dimensional then it will not use the optimization.
• There are other functions that use the same optimization such as MM_VLOOKUPOPTIMIZED and MM_HLOOKUPOPTIMIZED.

Example

Results for 10000 MATCHs (/ MM_MATCHOPTIMIZEDs) with table_array of different sizes:

The optimized function is more than 6 times faster for table_array of size 100,000 in our test case; the run time saved with the optimization increases with the size of table_array.