Description

Returns the value of the item corresponding to the lookupvalue in the corresponding column (works by row). Optimized runtime version of classic VLOOKUP function (without dimensional cells in range).

Syntax

=MM_VLOOKUPOPTIMIZED(Lookup_value, Table_array, Col_index_num, [Range_lookup])

• Lookup_value
  The value you want to lookup (could be a cell, string or number).
  
  
• Table_array
  Range. The range of cells in which the VLOOKUP will search for the lookup_value and return value.
  
  
• Col_index_num
  Integer. The column number (starting with 1 for the left-most column of the table_array).
  
  
• Range_lookup (optional)
  A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match. (1 for an approximate value or 0 for an exact match).

Remarks

• The difference with the default Excel function VLOOKUP is that the MM_VLOOKUPOPTIMIZED 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_VLOOKUPOPTIMIZED and VLOOKUP.
• 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_HLOOKUPOPTIMIZED and MM_MATCHOPTIMIZED.

Example

Results for 10000 VLOOKUPs (/ MM_VLOOKUPOPTIMIZEDs) 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.