Statistical
Get nth largest or smallest value—and matching name—with criteria (LARGE, SORT & FILTER)
Extract the 2nd, 3rd, or kth highest (or lowest) booking inside a specific team along with the rep's name using LARGE(FILTER(...), k) and INDEX(SORT(FILTER(...))).
MAXIFS only gives #1—use FILTER + LARGE / SORT to get #2, #3, and who won it!
Filter the requested segment before ranking. Here k counts records, including ties: with values 290,290,210 the second largest is 290, not the second distinct value 210.
The name formula sorts by value descending and then original row position ascending. Equal bookings therefore return names in source-row order. This explicit tie-breaker keeps the chosen name associated with the selected record.
This fixed six-row example requires every booking to be numeric and k to be a positive integer. Zero and negative bookings are allowed; blanks and numbers stored as text require correction. Segment labels must be nonempty text and are matched literally without wildcard interpretation. Matching is case-insensitive.
| Rep (A) | Segment (B) | Bookings (C) | Enterprise Standing |
|---|---|---|---|
| Mia | Enterprise | $290k | #1 Enterprise |
| Sam | Enterprise | $210k | #2 Enterprise (k=2 Largest -> "Sam", 210) |
| Eli | Enterprise | $175k | #3 Enterprise (k=2 Smallest -> 175) |
| Ava | Enterprise | $140k | #4 Enterprise |
| Leo / Nia | SMB | $65k / $88k | Excluded by B2:B7 = "Enterprise" |
nth Largest Value matching criteria (guarded with IFERROR)
Reject invalid k and source data first. No matching group or k beyond its size returns Rank Exceeds Group Size. Other engine errors are also caught by IFERROR, so inspect the source when troubleshooting.
=IF(OR(COUNT(C2:C7)<>6,NOT(ISNUMBER(F2)),NOT(ISTEXT(E2)),COUNTBLANK(E2)>0,NOT(ISTEXT(B2)),COUNTBLANK(B2)>0,NOT(ISTEXT(B3)),COUNTBLANK(B3)>0,NOT(ISTEXT(B4)),COUNTBLANK(B4)>0,NOT(ISTEXT(B5)),COUNTBLANK(B5)>0,NOT(ISTEXT(B6)),COUNTBLANK(B6)>0,NOT(ISTEXT(B7)),COUNTBLANK(B7)>0),"Invalid inputs",IF(OR(F2<1,F2<>INT(F2)),"Invalid inputs",IFERROR(LARGE(FILTER(C2:C7,B2:B7=E2),F2),"Rank Exceeds Group Size")))Name of the nth Largest Winner matching criteria
SORTBY orders filtered names by filtered booking values, then by original source position for equal values. The k-th record is returned.
=IF(OR(COUNT(C2:C7)<>6,NOT(ISNUMBER(F2)),NOT(ISTEXT(E2)),COUNTBLANK(E2)>0,NOT(ISTEXT(B2)),COUNTBLANK(B2)>0,NOT(ISTEXT(B3)),COUNTBLANK(B3)>0,NOT(ISTEXT(B4)),COUNTBLANK(B4)>0,NOT(ISTEXT(B5)),COUNTBLANK(B5)>0,NOT(ISTEXT(B6)),COUNTBLANK(B6)>0,NOT(ISTEXT(B7)),COUNTBLANK(B7)>0),"Invalid inputs",IF(OR(F2<1,F2<>INT(F2)),"Invalid inputs",IFERROR(INDEX(SORTBY(FILTER(A2:A7,B2:B7=E2),FILTER(C2:C7,B2:B7=E2),-1,FILTER(SEQUENCE(6),B2:B7=E2),1),F2),"Rank Exceeds Group Size")))The k-th smallest booking
SMALL uses the same validated dataset and positive integer k. Duplicate values occupy separate positions.
=IF(OR(COUNT(C2:C7)<>6,NOT(ISNUMBER(F2)),NOT(ISTEXT(E2)),COUNTBLANK(E2)>0,NOT(ISTEXT(B2)),COUNTBLANK(B2)>0,NOT(ISTEXT(B3)),COUNTBLANK(B3)>0,NOT(ISTEXT(B4)),COUNTBLANK(B4)>0,NOT(ISTEXT(B5)),COUNTBLANK(B5)>0,NOT(ISTEXT(B6)),COUNTBLANK(B6)>0,NOT(ISTEXT(B7)),COUNTBLANK(B7)>0),"Invalid inputs",IF(OR(F2<1,F2<>INT(F2)),"Invalid inputs",IFERROR(SMALL(FILTER(C2:C7,B2:B7=E2),F2),"Rank Exceeds Group Size")))Try the example
Use the original values or change them to test your understanding.