Searches for a specified item in the dimensions of a cell and returns the index of the dimension where the item is found.

Syntax

=MM_MATCH(Value, Valuecomp, Type)

• Value

  The value that you want to match in the list of values of Valuecomp.

• Valuecomp
  Cell. The list of values of a dimensional cell that is being searched.

• Type (optional)

  Type defines the criteria used for matching Value with Valuecomp.

  By default, the value is set to 1. The possible values are:

  1: MM_MATCH finds the largest value of Valuecomp that is less than or equal to Value.

  0: MM_MATCH finds the first value of Valuecomp that is exactly equal to Value.

  -1: MM_MATCH finds the smallest value of Valuecomp that is greater than or equal to Value.

  Warning: If Type is equal to 1 or -1, then Valuecomp must be sorted in descending and ascending order, respectively. You can do so by using the function MM_SORTDIM.

Example 1: Matching type is 0 (exact value)

In this example, we create in the table #Loops created from lookup_array a loop. For the first cell under column Loops, the value taken for the cell are the ones in the table #lookup_array. So, the first index of Loop for that cell will return 1, the second returns 34, the third 3 and the last 2.

The MM_MATCH function as seen below with Type (match type) and Value (lookup value) equal to 0 and 4 respectively returns 4. Indeed, the value 2 corresponds to the fourth index of Loop for Valuecomp.

Example 2: Matching type is either -1 or 1

In the following example, the second and third rows of #Loops created from lookup_array are using the values in tables #loopuk_array_2 and #loopup_array_3 respectively.

As we are using Type 1 and -1, the values of the lookup cells need to be sorted first. To do so, we use MM_SORTDIM in the table #Sorted values. To learn more about MM_SORTDIM, check this article.

If we check all available dimensions of the table #Sorted values, we see that the values were sorted through dimensions contrary to the table #Loops created from lookup_array.

The results are the following:

The largest value that is less than or equal to 60 in the cell lookup_array_2 is the third value, that is 34.

The smallest value that is greater than or equal to 36 in the cell lookup_array_3 is the second value, that is 46.