Description

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

Syntax

=MM_HLOOKUPOPTIMIZED(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 HLOOKUP will search for the lookup_value and return value.
  
  
• Col_index_num
  Integer. The row number (starting with 1 for the first row of the table_array).
  
  
• Range_lookup (optional)
  A logical value that specifies whether you want HLOOKUP 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 HLOOKUP is that the MM_HLOOKUPOPTIMIZED 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_HLOOKUPOPTIMIZED and HLOOKUP.
• 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_MATCHOPTIMIZED.

Example

Results for 10000 HLOOKUPs (/ MM_HLOOKUPOPTIMIZEDs) 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.