Lesson#69: LARGE and SMALL function in Excel | Get the Kth largest or Kth Smallest

When you want to get the smallest or the largest number from an array then it’s just using the function MAX and MIN. But if you want to get the Kth largest or Kth smallest then there are more two functions to get the result. These are LARGE and SMALL.

Function LARGE
The LARGE function returns the Kth largest value (numeric) in an array. Syntax of this function is LARGE (array, k)array – The array from which you want to find out the kth largest value.
k – An integer that specifies the kth position.

Now let’s see an example of LARGE.
Here I have some values in column C and I want to get the 2nd, 5th, and 4th largest values.

So I did put these formulas
LARGE($C$2:$C$15,2)
LARGE($C$2:$C$15,5)
LARGE($C$2:$C$15,4)
to find out the 2nd, 5th, and 4th largest value

Similarly, I did put these formulas
SMALL($C$2:$C$15,2)
SMALL($C$2:$C$15,5)
SMALL($C$2:$C$15,4)
to find out the 2nd, 5th, and 4th smallest values.

Now here I am having a table with some letters in the B column and some values in the C column. What if I need to find out the relevant values in a B column that meets the Kth largest value in the C column?

Then I just made a combination of INDEX, MATCH, and LARGE.

By putting this formula
=INDEX($B$2:$B$15,MATCH(LARGE($C$2:$C$15,2),$C$2:$C$15,0))
=INDEX($B$2:$B$15,MATCH(LARGE($C$2:$C$15,5),$C$2:$C$15,0))