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 (๐ฎ๐ฟ๐ฟ๐ฎ๐๐ญ, ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ฎ)
โข ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ญ โ the first array to multiply
โข ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ฎ โ the second array to multiply
The rules of MMULT are:
โข only numbers are permitted in each array
โข the number of columns in ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ญ must match the number of rows in ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ฎ
The matrix product returned will consist of the same number of rows as ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ญ, and the same number of columns as ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ฎ.
At the top are two matrices: Matrix A and Matrix B. The MMULT Method multiplies ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ญ (Matrix A) by ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ฎ (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. ๐น๐ผ๐ผ๐ธ๐๐ฝ_๐๐ฎ๐น๐๐ฒ
1 is the value to find.
2. ๐น๐ผ๐ผ๐ธ๐๐ฝ_๐ฎ๐ฟ๐ฟ๐ฎ๐
MMULTโs ๐ฎ๐ฟ๐ฟ๐ฎ๐๐ญ 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).
๐ฎ๐ฟ๐ฟ๐ฎ๐๐ฎ 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 ๐น๐ผ๐ผ๐ธ๐๐ฝ_๐๐ฎ๐น๐๐ฒ.
Put together, the result is a 1 x 10 array of Booleans with a solitary 1 in the column England is in (M26).
3. ๐ฟ๐ฒ๐๐๐ฟ๐ป_๐ฎ๐ฟ๐ฟ๐ฎ๐
Where the final value comes from, which is the range containing the group letters.
The formula culminates by returning group I in J11.
