MMULT Function
Excel functions like XLOOKUP and MATCH lack the ability to look up values in multiple rows or columns. However, this problem is circumvented with the help of MMULT, which returns the matrix product of two arrays.
Before diving into the main example, let’s first understand how MMULT works (page 1).
The syntax is:
MMULT (array1, array2)• array1 — the first array to multiply
• array2 — the second array to multiply
The rules of MMULT are:
• only numbers are permitted in each array
• the number of columns in array1 must match the number of rows in array2
The matrix product returned will consist of the same number of rows as array1, and the same number of columns as array2.
At the top are two matrices: Matrix A and Matrix B. The MMULT Method multiplies array1 (Matrix A) by array2 (Matrix B) to generate a new array comprising two rows and four columns (2 x 4).
The Manual Method shows two examples of how you would do this without MMULT. It involves multiplying each row cell in Matrix A by each equivalent column cell in Matrix B.
Page 2 shows a table containing 2022 FIFA World Cup qualification groups. If you wanted to find which one England were in, you might be tempted to use a formula like:
=INDEX(A2:J2, , MATCH(J10, A3:J8, 0))=XLOOKUP(J10, A3:J8, A2:J2)
However, neither would work.
Instead, XLOOKUP can be used in conjunction with MMULT, SEQUENCE and ROWS to return the desired result.
The three XLOOKUP arguments are:
1.
lookup_value
1 is the value to find.
2.
lookup_array
MMULT’s array1 argument utilises SEQUENCE and ROWS to construct a 1 x 6 array of 1 values to convert the number of team rows into columns (M15).
array2 then carries out a conditional check on the team data to find which value is equal to ‘England’ (M18). The result is a 6 x 10 array of zeros — except the matching value.
Multiplying the first array by the second means the calculation will be 1 x 0 or 1 x 1. As only the latter can equal 1, this matches the lookup_value.
Put together, the result is a 1 x 10 array of Booleans with a solitary 1 in the column England is in (M26).
3.
return_array
Where the final value comes from, which is the range containing the group letters.
The formula culminates by returning group I in J11.
