User profile for user: nrhodes
nrhodes Author
User level: Level 1
4 points

Formulas: Top 4 times and athlete names

I need some help with formulas. I am attempting to set up a spreadsheet for track and field events. One of the things I am trying to do is find the 4 fastest times for each event. I used SMALL (E2:E35), 1 to find the fastest, then SMALL (E2:E35), 2 or SMALL (E2:E35), 3 and SMALL (E2:E35), 4. However, this only gives me the duration for each event. Is there a way to use a formula to get for the duration (column E) and the name of the person to whom the duration belongs (column A). From week to week there athletes will get faster and the fastest four athletes will not always be the same.

Posted on Mar 20, 2026 6:49 PM

Reply
Question marked as Top-ranking reply
User profile for user: Camelot
Camelot
User level: Level 9
59,205 points

Posted on Mar 21, 2026 3:42 PM

Yellowbox shows one way - here's another.



This uses a FILTER() function in Table 2::A1 to select all rows in Table 1::A:B where the value in Table 1::B is less than or equal to the 4th-smallest value in that column.


=FILTER(Table 1::A:B,Table 1::B≤SMALL(Table 1::B,4))


As written, the names will be listed in the same order they appear in Table 1. if you want them sorted by time, add a SORT() function:



=SORT(FILTER(Table 1::A:B,Table 1::B≤SMALL(Table 1::B,4)),2)
View in context
6 replies
Question marked as Top-ranking reply
User profile for user: Camelot
Camelot
User level: Level 9
59,205 points

Mar 21, 2026 3:42 PM in response to Yellowbox

Yellowbox shows one way - here's another.



This uses a FILTER() function in Table 2::A1 to select all rows in Table 1::A:B where the value in Table 1::B is less than or equal to the 4th-smallest value in that column.


=FILTER(Table 1::A:B,Table 1::B≤SMALL(Table 1::B,4))


As written, the names will be listed in the same order they appear in Table 1. if you want them sorted by time, add a SORT() function:



=SORT(FILTER(Table 1::A:B,Table 1::B≤SMALL(Table 1::B,4)),2)
Reply

Formulas: Top 4 times and athlete names

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up