Returns the element of a table corresponding to a specified combination of criteria for which the first columns of that table are tested. The criteria must be values or references to values. This function is similar to a lookup function with one or multiple criteria.

Syntax

=MM_READTABLE(Range, Header, Comparison1, [Comparison2], ...)

• Range
  The table which contains the value you are looking for and the columns to be tested for the criteria. Headers must be included.

• Header
  Header of the column of Range from which to return the desired value. You can only choose one header.

• Comparison1
  First value to be tested in the first column of the table Range.

• Comparison2 (optional)
  Second value to be tested in the second column of the table Range.

• ... (optional)
  Other values to be tested. You can input a maximum of 6 comparisons.

Remark

• If several values meet the combination of criteria, the first value in the table is returned.

• If no row in Range meets the criteria, the function returns the following message: "Combination of criteria Comparison1, Comparison2, ..., etc. not found in table TableReference", where TableReference is the position and the name of the grid from which Range is extracted. If you would like to return the generic error "NaN", you can use the function MM_READTABLENAN instead.

Example 1: Basic example

In this example, three columns are used to select the desired output.

Formula1: =MM_READTABLE(A3:E11,"O-1","A","B","A")
Returns 3. Indeed, the "O-1" value corresponding to the combination (A,B,A) is 3.

Formula2: =MM_READTABLE(A3:E11,E3,"A","B","B")
Returns "TYPE-05". Indeed, the "O-2" value corresponding to the combination (A,B,B) is "TYPE-O5".

Formula3:=MM_READTABLE(A3:E11,"O-1","A","B")
Returns 3. Note that only two criteria were specified. Thus, "3" and "4" are values that meet the criteria. "3" is returned because it is placed before "4" in the table. 

Formula4: =MM_READTABLE(A3:E11,"O-1","A","B","C")
Returns "Combination of criteria "A,B,C" not found in table "Sheet1 > Table"".

Example 2: Array formula

To optimize run time, you can use MM_READTABLE as an array formula. We use the previous grid #Table with the 3 first columns as criteria.

Below is an example of MM_READTABLE used without array formulas. The formulas in K3 and L3 are copied to the rows below.

We have replaced the 4 individual formulas by an array formula using either a fixed range (see column K) or a dynamic range with MM_COLUMN (see column L). Those formulas are more optimized as Mind will perform the calculation only once per array formula.

It is not mandatory to input a full range in the array formula for all criteria. If your criterion remains the same for each row, you can have either a hardcoded value or a single cell.