How to Find the Second Highest or Lowest Value in Google Sheets Using LARGE and SMALL

Standard summary functions in Google Sheets—such as MAX and MIN—are perfect for extracting the absolute highest and lowest numbers across a dataset. However, real-world reporting frequently demands extracting secondary metrics—such as identifying the runner-up in a sales contest, discarding the single highest outlier score before averaging, or discovering the second cheapest supplier quote. Instead of sorting tables manually or writing intricate nested IF statements, Google Sheets provides the native LARGE and SMALL functions to query any Nth rank directly.

Extracting the Second Highest Value with LARGE

The LARGE function evaluates a numeric array and returns the value at any designated position relative to the maximum:

=LARGE(data, n)

• data: The range of cells containing the numbers you wish to evaluate (e.g. B2:B100).
• n: The rank position you wish to retrieve (e.g. 1 for highest, 2 for second highest, 3 for third highest).

To extract the second highest value from scores in column B:

1. Select an empty destination cell.
2. Enter the formula:

   =LARGE(B2:B100, 2)

3. Press Enter.

If the highest score is 98 and the next score is 94, the formula returns 94 immediately.

Extracting the Second Lowest Value with SMALL

Conversely, the SMALL function identifies values ranked from the bottom of the scale upward:

=SMALL(data, n)

To determine the second lowest quote among contractor estimates in column C:

1. Click the target reporting cell.
2. Enter:

   =SMALL(C2:C50, 2)

3. Press Enter.

Using n = 1 returns the absolute minimum (identical to MIN), while n = 2 returns the second lowest estimate.

Handling Duplicate Values with UNIQUE

When multiple records share identical top scores (for example, two employees tied for first place with 100 points), standard LARGE(B2:B100, 2) returns 100 because it ranks occurrences positionally rather than distinct values. To retrieve the second distinct highest value, nest the range inside UNIQUE:

=LARGE(UNIQUE(B2:B100), 2)

The UNIQUE formula filters out duplicate scores before LARGE evaluates rank, ensuring you receive the true second-tier value (such as 95 rather than the tied 100).