The Min and Max statistical functions can take a matrix as an argument to calculate the minimum or maximum value. But when you use Min or Max in a matrix in Sheets and want to return a value from the same row, you need to code a formula.
The Min, Max functions can’t perform that and there is no built-in function in Sheets for such a typical use. If you have such a problem, in this post, I am going to help you code the required formula for you.
What about multiple min/max numbers in the matrix?
If there are multiple min/max numbers in the matrix, the formula would return values from all the rows containing the min/max. See the following illustration that uses ‘Max’.
Sheets Formula to Find Min, Max in a Matrix and Output a Value from the Same Row
I have a 3×3 matrix (B3:D5) which contains the marks of three students in different terms. How can I find the name of the student who scored max as well as min in the exams?
For this, I have a formula which uses Max, Search, MMULT, and Filter as the main functions.
Don’t jump into conclusion that the formula is difficult to follow. You will get the step-by-step instructions to code the formula below.
Max in a Matrix and Get the Name from the First Column
Below are the steps to find the max marks in a matrix (B3:D5) and return the name of the student from the same row in a column (A3:A5) in Sheets.
Step # 1: Finding Max Value in a Matrix
To find the Max value in a matrix, you can use the Max function as below.
=max(B3:D5)Step # 2: Searching Max Value in a Matrix to Identify Multiple Max Values
Use the above max value as the search key in a Search formula. This will help us to identify the cell(s) containing the max values in the matrix.
=ArrayFormula(search(max(B3:D5),B3:D5))For explanation purpose, I have keyed the above formula in cell B9. The #1 in the output represents max values.
Step # 3: Replacing Error Values in the Matrix with Zero
Just wrap the above formula with Iferror and then by the function N. This will replace all the #VALUE! errors with zeros.
Note: Always move the function ArrayFormula to the beginning. Also, in most of the cases, there is no need to use multiple ArrayFormula functions in a combination formula.
=ArrayFormula(N(IFERROR(search(max(B3:D5),B3:D5))))You can call this formula output as ‘virtual matrix’. I want to sum each row in this output as below.
To do that, you can use the MMULT function in Sheets which I have detailed under the Step # 4 below.
Step # 4: MMULT to Create a Filter Criteria Column to Filter the Name
Please don’t get confuse with the title of this step. I am going to explain that.
MMULT Syntax/Arguments:
MMULT(matrix1, matrix2)I am going to use the above ‘virtual matrix’ as matrix 1 in MMULT. Then what about the matrix 2?
The matrix standard says “the number of columns for matrix 1 must equal the number of rows for matrix 2”. We want to sum each row, right? Then use the below formula as matrix 2 which populates the number 1 thrice in a column.
=ArrayFormula(row(A3:A5)^0)
MMULT Formula:
=ArrayFormula(mmult(N(IFERROR(search(max(B3:D5),B3:D5))),row(A3:A5)^0))
This output is our filter criteria column. Yes I am going to use this in a Filter formula.
Step # 5: Filter All the Rows Containing the Max Value
In the above MMULT output, you can see # 1 in the second and third row. That means these rows contain the max values in the source. If the value is 0, then there is no max value in that row.
If there are two max values in the same row in the matrix (B3:D5), then the formula would return # 2 in that row.
That means to get the name of the max scorers, you should filter out the row that contains 0 in the MMULT output.
You can Filter the first column (A3:A5) using the above MMULT as the criteria. How?
Filter Syntax/Arguments:
FILTER(range, condition1)range – A3:A5
condition: mmult(N(IFERROR(search(max(B3:D5),B3:D5))),row(A3:A5)^0)>=1
Final Formula:
=filter(A3:A5,mmult(N(IFERROR(search(max(B3:D5),B3:D5))),row(A3:A5)^0)>=1)Find Min in a Matrix and Return Name from the First Column
Just replace the Max function in the above final formula with the Min function. That’s the only thing that you want to find Min in a matrix and return a value from the same row in Sheets.
