With the help of an array formula, we can find the second highest value from every group in Google Sheets.

If you just want to find the second highest value like the second highest salary from a column, then you can use the function LARGE in Google Sheets.

It would be as below for column B.

=large(B:B,2)

You can change the column reference B:B if the salaries are in a different column.

Now take a look at the below table that contains scores of students in a sports event.

I have marked the second-highest scores of every student in column C.

I want to extract those corresponding rows (row numbers 2, 8, and 11) using an array formula in Google Sheets.

It’s not a difficult task if you know the logic. Here are the steps/logic in short.

1. Sort column A (Name) in ascending order and column C (Score) in descending order.

2. Use Countifs to find the running count of sorted column A (names).

3. Filter step 1 result if the step 2 result is equal to 2.

We will include all the above three steps in a single formula to find the second highest value from every group in Google Sheets. Here we go!

Formula to Find the Second Highest Value from Every Group

For the above sample data, we can use the below combination formula.

=filter(
     sort(A2:C,1,1,3,0),
     COUNTIFS(sort(A2:A),sort(A2:A),ROW(A2:A),"<="&ROW(A2:A))=2
)

Can You Split this Formula and Explain?

Yes! Here is the explanation of the above formula.

There are two main steps in the formula. They are Sort and Countifs. The last step is filtering.

First of all, let’s test what the Sort formula does.

Step 1:

For the explanation purpose, I am splitting the above formula and inserting them in individual cells.

The SORT formula in cell E2 (please refer to the image below), i.e. =sort(A2:C,1,1,3,0), sorts the names in ascending order and scores in descending order.

So the names will be in alphabetical order and the highest scores will be on the top (please see E2:H on the image below).

Step 2:

The COUNTIFS-based running count formula in I2, i.e. =ArrayFormula(COUNTIFS(sort(A2:A),sort(A2:A),ROW(A2:A),"<="&ROW(A2:A))), returns the running count of the names (please see I2:I in the image above).

Step 3:

The step 3 formula helps us to find the second highest value, i.e. scores, from every group.

See the step 3 formula in a generic form.

=filter(step_1,step_2=2)

That means the FILTER formula filters the step 1 formula result if the step 2 formula result is equal to 2.

Note:

• As you can see, my source data in A1:C is already sorted by names, but not by scores. My formula to find the second highest value from every group in Google Sheets can be used in sorted or unsorted source data. No matter whether the Names are sorted or unsorted. That’s why I have included Sort within the formula.
• To get the third largest value from every group, you can change criterion 2 in the last part of the formula with 3. That means the above formula is useful to filter the nth highest value from every group in Google Sheets.
• Instead of scores, you can use the same formula in a date or timestamp column.
• Instead of the 2nd largest value, you can change the formula to return up to the 2nd largest value by changing =2 with <=2.

Second Largest Values from Every Group Skipping Duplicates

In the above source data, let’s assume, the scores of “Mark” (a name in column A) are as below (others’ scores remain the same).

Here the second largest value will be 18 as per our formula above. Because the highest value 18 repeats twice.

I want to skip duplicates. What to do?

Assume the source data is in A1:C in the tab named ‘Data’. If so use the below SORTN formula in A2 in a new tab named ‘Result’.

=SORTN(Data!A2:C,9^9,2,1,1,3,0)

Then use our same filter formula in E2 in the ‘Result’ tab, not in the ‘Data’ tab. You will get the correct result.

This method has one issue. If all the scores in a group are the same, for example, all the scores of “Mark” are 18, then the result won’t have the second-highest score of “Mark”.

As I have already mentioned, the above same formula can be used in a date or timestamp column instead of a number column. So that you can find the second largest date or timestamp from every group.

That’s all about how to find the second highest value from every group from a table or list in Google Sheets.

Thanks for the stay. Enjoy!