Statistical
LARGE and SMALL with criteria: Nth highest or lowest value by group
Combine LARGE or SMALL with FILTER to retrieve the 2nd largest deal or 2nd fastest time inside a category—beyond MAXIFS and MINIFS.
Go beyond 1st place (MAXIFS / MINIFS) to 2nd and 3rd place
MAXIFS and MINIFS return only rank 1. To find the runner-up (2nd largest) deal in the "Enterprise" tier, filter DealValue by Tier = "Enterprise" and pass that subset into LARGE(..., 2).
Guarding SUMPRODUCT(--(A2:A5=C2)) < D2 avoids #NUM! when a group has fewer deals than the requested rank K.
| Tier (A) | DealValue (B) | Enterprise Rank |
|---|---|---|
| Enterprise | 50000 | 3rd largest (1st smallest) |
| SMB | 90000 | (excluded) |
| Enterprise | 120000 | 1st largest |
| Enterprise | 85000 | 2nd largest |
Conditional LARGE and SMALL formulas
Among Enterprise deals [50k, 120k, 85k], rank 2 in LARGE is $85,000 and rank 2 in SMALL is also $85,000 (rank 1 in SMALL is $50,000).
=IF(OR(D2<1, D2<>INT(D2)), "Invalid rank", IF(SUMPRODUCT(--(A2:A5=C2)) < D2, "Not enough rows", LARGE(FILTER(B2:B5, A2:A5=C2), D2)))Conditional SMALL formula
SMALL(FILTER(B2:B5, A2:A5=C2), 1) matches MINIFS ($50,000), while k = 2 gives the 2nd smallest.
=SMALL(FILTER(B2:B5, A2:A5=C2), 1)Inputs and interpretation
Deal values must be numeric and nonempty. The rank must be a positive integer; duplicate values occupy separate ranks. The literal equality guard uses the same matching rule as FILTER, so * and ? in category names are not wildcards. SMALL without a guard returns an error for an absent category.
Try the example
Use the original values or change them to test your understanding.