HomeGoogle DocsSpreadsheetReturn First and Second Highest Values in Each Row in Google Sheets

Return First and Second Highest Values in Each Row in Google Sheets

Published on

Using the DMAX function we can return the first and second highest values in each row in Google Sheets. This post explains how.

The dedicated function to get the nth largest value in Google Sheets is LARGE. But, it won’t work as an array formula.

At the time of writing this tutorial, there is no other built-in function to return the first and second highest values in each row. So I have decided to write a formula using the DMAX function for the said purpose.

Update:- A relatively new function, which is based on lambda, is available for this purpose. I’ve included that at the end of this tutorial.

The formula that I am going to write will be simple to understand. But to make you fully understand the formula I will walk you through step-by-step instructions.

Let’s code an array formula to return the first and second-largest values or we can say MAX1 and MAX 2 values in Google Sheets.

Sample Data:

Getting First and Second Highest Values in Each Row

Array Formula for Getting First and Second Highest Values in Each Row

To get the second-highest value in each row using an array formula is so simple if you know how to get the first highest value in each row.

So first we will write the formula to return the first highest value in each row.

There is already a tutorial on this site for the said purpose here – How to Find Max Value in Each Row in Google Sheets [Array Formula].

But I am not recommending that. It may not work if your sheet contains hundreds of rows. Instead, I am going to use DMAX similar to DMIN which I have used earlier here – Row-Wise MIN Using DMIN in Google Sheets.

The formula credit goes to lightmare, one of my readers.

Here we go!

Two Non-Array Formulas Using the LARGE Function

Here are the steps to return the first highest value in each row. I mean using non-array formulas.

The data to return the first and second highest values in each row are in the range B3:G7.

Before going to the array formula, here are the non-array-formulas based on LARGE.

In cell H3, copy-paste the below formula. Then drag it down.

=large(B3:G3,1)

In cell I3, copy-paste the below formula. Then drag it down.

=large(B3:G3,2)

Using the non-array formulas is OK if you have a few rows. But if your data has several rows, it’s best to use array formulas.

It will benefit you in several ways. For example, if you insert rows, the array formula will return the result in that row too. But in the case of non-array formulas, you may need to copy-paste the formula from the row above or down.

Array Formula to Get the First Highest Values in Each Row (DMAX)

Syntax: DMAX(database, field, criteria)

Normally all the database functions require the field labels (header row) to function correctly. In our sample data above, we have field labels in B2:G2 (seller names).

You need to understand some basics before proceeding further. What’s that?

We can use DMAX for column-wise max in an array as below.

=ArrayFormula(dmax(A2:G7,{2,3,4,5,6,7},A2:A7))

In this formula, the range A2:G7 is the database, {2,3,4,5,6,7} (the columns from which we want to return the max values) is the fields and A2:A7 is the criteria.

Column-Wise Max - DMAX in Sheets

Here you can replace the field numbers {2,3,4,5,6,7} with the formula sequence(1,columns(B2:G2),2) also.

The above are the basics to understand how to return the first and second highest values in each row in Google Sheets.

To return the First Highest Values in Each Row, we can transpose the range B3:G7 and then apply the above column-wise max. That’s the logic I am following and going to explain below.

When doing so we may not have the required field labels (header row) or the criteria. So that we will use some workaround. Here are the steps.

I hope the screenshots along with the explanations will be useful for you to grasp the steps.

Steps to Follow (With Screenshots)

The below steps are for explanation purposes. We will combine the steps in the final formula to remove extra cells or cell range.

1. In cell A10, add a row number column with the range B3:G7. Because we are going to use only the numeric columns in DMAX.

=ArrayFormula({sequence(rows(B3:B7),1),B3:G7})
Adding Sequence Column to Table - Step 1

We have replaced the fruit names in the cell range A3:A7 with sequential numbers from 1 to 5.

2. Just TRANSPOSE the above step 1 data orientation.

=ArrayFormula(TRANSPOSE({sequence(rows(B3:B7),1),B3:G7}))
The Key to Get First and Second Highest Values in Each Row

Above is the table (B10:E16) for DMAX use.

3. Let’s find the column-wise max of this table. The result will be equal to the highest values in each row of table B3:G7. So we should insert the formula in cell H3.

The arguments will be;

database: B10:E16

field: sequence(rows(B3:B7),1) which is the first column added to the above table in step # 1.

criteria: {IF(,,);IF(,,)} – We have no criteria column. So used two blank cells (the field labels are blank and the criteria are also blank). I got this idea from the user “nightmare”.

So the formula to use in cell H3 will be;

=ArrayFormula(dmax(A10:E16,sequence(rows(B3:B7),1),{IF(,,);IF(,,)}))

Further to remove the helper range A10:E16, replace the same in the formula with the formula from cell A10.

=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B7),1),B3:G7}),sequence(rows(B3:B7),1),{IF(,,);IF(,,)}))

The above is the formula to get the first highest values in each row in Google Sheets.

Array Formula to Get the Second Highest Values in Each Row (DMAX)

Now getting the second highest values in each row is so simple.

Insert the same above formula in cell I3.

Then we should replace B3:G7 in the I3 formula with another formula.

Didn’t get it?

We have the first largest values in H3:H7 right? We should test these values in the range B3:G7. Then replace the matching values with 0s.

It sounds complicated, but a simple IF logical formula can do that.

Here is that formula;

if(B3:G7=H3:H7,0,B3:G7)

So the formula to return the second largest values in each row is as follows (insert this formula in cell I3).

=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B7),1),if(B3:G7=H3:H7,0,B3:G7)}),sequence(rows(B3:B7),1),{IF(,,);IF(,,)}))

How Can I Include More Rows in the DMAX Formulas?

I know that you may want to use the above formulas in a very large data range.

The above DMAX formulas will be a perfect fit for a large dataset. Feel free to open the range in the formulas.

Note:- Delete all the columns and rows that are not in use. Insert rows and columns as and when required.

H3 Open Range Formula:

=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B),1),B3:G}),sequence(rows(B3:B),1),{IF(,,);IF(,,)}))

I3 Open Range Formula:

=ArrayFormula(dmax(TRANSPOSE({sequence(rows(B3:B),1),if(B3:G=H3:H,0,B3:G)}),sequence(rows(B3:B),1),{IF(,,);IF(,,)}))

These formulas may return 0 values as the first and second-largest values against the rows that are entirely blank.

So, as every Google Sheets pro users do, we can use the IF function with the above to fine-tune it.

Just insert if(B3:B="",, or if(A3:A="",, within the start of the formula.

You can find that within my sample sheet below.

BYROW With LARGE to Return Nth Max Value in Every Row

One of the problems with the above DMAX is that it falls short when we want to find the nth max value in every row because we have no way to specify nth in the said database function.

See what method we have adopted above to find the 2nd max value in every row.

Now it’s so simple as we can use the LARGE function in a row and expand it using the BYROW function.

In cell J3, insert the following formula.

=byrow(B3:G,lambda(row,iferror(LARGE(row,1))))

The one in vivid red in the last part defines ‘n’ in the formula.

To get the max 3 in every row, replace 1 with 3.

That’s all. Thanks for the stay. Enjoy!

Sample_Sheet_16221

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here