Find Min, Max in a Matrix and Return a Value from the Same Row in Sheets

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’.

Max in a matrix and return a value from the same row

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.

Search for max or min values in an array

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.

Sum a matrix in Google Sheets

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)
Virtual arrays to use in MMULT

MMULT Formula:

=ArrayFormula(mmult(N(IFERROR(search(max(B3:D5),B3:D5))),row(A3:A5)^0))
Sum of each Row in a matrix

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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.