MMULT Function
Returns the matrix product of two arrays. It is useful for weighted scoring, matrix transformations, and matrix checks in Excel.
Summary
MMULT returns the matrix product of two arrays. Instead of multiplying cells one by one in the usual spreadsheet way, it multiplies rows from the first array by columns from the second array and adds those products together.
This is why the function is useful for weighted scores, coordinate work, and matrix checks. It can do a lot in one formula, but the shape of the inputs has to make sense first.
Purpose
Combines two numeric arrays using matrix multiplication rules.
Return Value
The result spills to a range whose size depends on the two input shapes.
Syntax
=MMULT(array1, array2)The number of columns in array1 must match the number of rows in array2. Microsoft also notes that MMULT returns #VALUE! when the dimensions do not line up or when the arrays contain text or empty cells.
Arguments
- array1 - Required. The first numeric array.
- array2 - Required. The second numeric array.
MMULT vs Other Multiplication Options
Not every multiplication task needs MMULT. It is best when you actually want matrix behavior, not just basic arithmetic.
| Tool | What it does | Returns | Use it when |
|---|---|---|---|
* |
Multiplies simple values | One value | You just need ordinary arithmetic |
SUMPRODUCT |
Multiplies matching arrays and sums them | One value | You want one weighted total |
MMULT |
Performs matrix multiplication | An array | You need a real matrix product |
TRANSPOSE |
Flips rows and columns | An array | You need to reshape an array so MMULT can work |
Using MMULT
The first thing to check is the dimensions. If the first array is 3x2 and the second array is 2x2, the multiplication works. If the first array is 3x2 and the second is 3x2, it does not. That shape check matters more than the actual numbers when you are starting out.
Once the dimensions line up, MMULT becomes very practical. You can use it for scoring models, for verifying matrix inverses, or for compact formulas that would otherwise take a lot of helper cells. It is also a good example of why matrix formulas feel different from normal Excel formulas. The result is often a whole range, not one cell.
Example 1 - Multiply two 2x2 matrices
This is the standard starting example. Both arrays are square, and the inner dimensions match, so the multiplication is valid.
=MMULT(B2:C3,E2:F3)Multiply the matrix in B2:C3 by the matrix in E2:F3. Formula: =MMULT(B2:C3, E2:F3).
Example 2 - Multiply a row by a matrix
A 1x2 row can multiply a 2x2 matrix because the inner dimension is still 2. The result comes back as a 1x2 array.
=MMULT(B2:C2,E2:F3)Multiply a 1x2 row (B2:C2) by a 2x2 matrix (E2:F3). Formula: =MMULT(B2:C2, E2:F3).
Example 3 - Predict the result size
If the first array is 3x2 and the second is 2x2, the output will be 3x2. Getting comfortable with that pattern makes MMULT much easier to use.
=MMULT(B2:C4,E2:F3)Verify if a 3x2 matrix can multiply a 2x2. Result should be 3x2. Formula: =MMULT(B2:C4, E2:F3).
Example 4 - Combine MMULT with TRANSPOSE
Sometimes the array shape you need is not already there. TRANSPOSE can flip it so the multiplication works.
=MMULT(TRANSPOSE(B2:B3),B2:B3)Multiply the transpose of B2:B3 (2x1) by B2:B3. Formula: =MMULT(TRANSPOSE(B2:B3), B2:B3).
Conclusion Recap
- Main job:
MMULTreturns the matrix product of two numeric arrays. - Key rule: Columns in the first array must match rows in the second array.
- Typical error:
#VALUE!shows up when dimensions do not line up or the arrays contain text or blanks. - Result shape: Rows come from the first array, columns come from the second.
- Helpful partner:
TRANSPOSEis often useful when the orientation is wrong.
Tell your friends about this post