Lesson#69: Get the Kth largest or Kth Smallest

When you want to get the smallest or the largest number from a 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 function to get the result. These are LARGE and SMALL.Function LARGE
The LARGE function returns the Kth largest value (numeric) in a 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.

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

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

 

So I did put this 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, 4th largest value

 

 

Similarly I did put this 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, 4th smallest value.

Now here I am having a table with some letters in B column and some values in C column. What if I need to find out the relevant values in an B column that meets the Kth largest value in 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))

=INDEX($B$2:$B$15,MATCH(LARGE($C$2:$C$15,4),$C$2:$C$15,0))







Related Video Tutorials:

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

Tagged with: , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*