How to get nth values with SMALL and LARGE

This would be, for example, the 1st, 2nd, and 3rd smallest or largest values.

In this first sheet, we have a list of students with five test scores.

I’ll use theLARGE functionto extract the three best scores for each student.

To get top values, use theLARGE function.

Then I can copy the formula across and come back and change “k” as needed.

When I copy all three formulas down, we get the three best scores for each student.

So, let me show you a few tricks for handling “k” in this situation.

That lets me copy the same formula everywhere.

Then I can use this same construction directly in the formula everywhere.

Now let’s take a look at theSMALL function.

Here we have a list of race results, and I want to extract the three best times overall.

The best times are the shortest times, so this is a job for theSMALL function.

I’ve already got a rank in column D using theRANK function, which I’ll leave for reference.

I also havenamed rangesfor “names” and “times.”

These will make the formulas a bit easier to read.

When I copy the formula down, we get the three shortest, or best, times.

So, you might wonder at this point how can we get the name for each of these times?

And for that, we can useINDEXandMATCH.

MATCHwill give us the position of each item in the list.

So I can just take that MATCH formula, and use it directly inside ofINDEXto fetch the name.