With the help of the BYROW and LARGE functions, you can easily find the first, second, or nth highest value in each row in Google Sheets.

Sample Data

Assume you have item descriptions in one column and sales from different regions in the adjacent columns. You can then find each item’s first, second, and third highest sales across these regions. Below is an example of how your data might look:

The above data is in the range A1:E5. Let’s find the first, second, and third highest values in each row and display them in F2:F5, G2:G5, and H2:H5, respectively.

Regular Non-Array Solutions

Finding the First Highest Value:

To find the first highest value for each row, enter the following LARGE formula in cell F2 and drag the fill handle down:

=LARGE(B2:E2, 1)

Finding the Second Highest Value:

To get the second highest value, replace 1 with 2 in the formula, and then drag it down:

=LARGE(B2:E2, 2)

Finding the Third Highest Value:

For the third highest value, replace 1 with 3 in the formula and drag it down:

=LARGE(B2:E2, 3)

Array Formula to Find the First, Second, and Third Highest Values in Each Row

When you want to consider a row, rather than a single cell, as a single entity and apply a LAMBDA function to each row, you can use the BYROW function.

The syntax is:

BYROW(array_or_range, lambda)

In this case, the array or range is B2:E5. Let’s write the LAMBDA function to apply to each row.

Finding the First Largest Value

We will start by coding the formula to find the first largest value in each row. For this, we will use the following LARGE formula, which we used earlier in cell F2:

=LARGE(B2:E2, 1)

We can convert this to a LAMBDA function, which follows the syntax:

LAMBDA([name, …], formula_expression)

In the LARGE formula, replace B2:E2 with a meaningful name, such as row, and define that in LAMBDA as follows:

LAMBDA(row, LARGE(row, 1))

The LAMBDA function to find the first largest value in each row is now ready. Simply use this within the BYROW function as follows:

=BYROW(B2:E5, LAMBDA(row, LARGE(row, 1)))

Clear any values in F2:F5 and enter this formula in F2 to get the first largest value in each row.

Finding the Second and Third Largest Values

To get the second, third, or nth largest value in each row, replace 1 with n, such as 2, 3, etc. For example:

1. For the second largest value:
   =BYROW(B2:E5, LAMBDA(row, LARGE(row, 2)))
2. For the third largest value:
   =BYROW(B2:E5, LAMBDA(row, LARGE(row, 3)))

Resources

• How to Find Max Value in Each Row in Google Sheets
• Find Max N Values in a Row and Return Headers in Google Sheets
• Get Max Date in Each Row Using an Array Formula in Google Sheets
• Row-Wise MIN Using DMIN in Google Sheets
• Get Min Date Ignoring Blanks in Each Row in Google Sheets