Filters specified ranges with a given key.

Syntax

=MM_FILTER(Key, Output1, [Output2], [Output3])

• Key
  Range that contains values equal to 0 or 1:
  - 0 to omit the value,
  - 1 to return the value.
  Key must have only one column.

• Output1
  First range to return and filter according to Key. Output1 can have several columns but must have at least the number of rows of Key. 

• Output2 (optional)
  Second range to return and filter according to Key. Output2 can have several columns but must have at least the number of rows of Key.

• Output3 (optional)
  Third range to return and filter according to Key. Output3 can have several columns but must have at least the number of rows of Key.

Remarks

• This function is not meant to be used inside another function. It must be used alone in a cell.

• Warning: Key and Output ranges must have the same dimensions for it to finish calculations properly. Otherwise, you may get an error message like the following: Application can't finish calculation of the model. Limit of 100 calculation iteration has been reached.

• Warning: In Excel, once a cell has been updated using this function, you cannot cancel this action.

• Warning: The column, just after the right boundary of the table which contains the MM_FILTER must be empty, otherwise datas on the right of the table will be deleted. It is similar to the lower boundary as the MM_FILTER function is meant to resize the table on the right and below. However columns on the left will be correctly resized. Thus, it is best to always leave an empty column after your table and at least as much rows available than the filtered table.

• Notice that the function MM_GETRANGE will automatically appear in your table. This function is used as an intermediate step by some functions from the add-in. It has no other purpose.

• Best practice is to input headers manually, in other words, Output1, Output2, and Output3 must not include headers or it may cause incorrect outputs.

Example 1: Basic example

Consider the following table. We want to filter it using the key which corresponds to the second column of the table. 

Formula in #Filtered Table(2,1): =MM_FILTER(B2:B10,E2:F10,A2:A10)
Returns the following table: 

Notice that the rows returned correspond to the ones for which the key value is equal to 1. You can output one or several columns. They do not need to be in the same table. 

Example 2: Bi-dimensional table

You can do the same with a multi-dimensional table. Let us consider a bi-dimensional table.

The values for the first dimension are:

The values for the second dimension are: