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 is the value to find.
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).
Where the final value comes from, which is the range containing the group letters.
The formula culminates by returning group I in J11.