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

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.

Function SMALL
The SMALL function returns the Kth smallest value (numeric) in an 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 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.

small function in Excel

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?

small function in Excel

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))

small function in Excel
=INDEX($B$2:$B$15,MATCH(LARGE($C$2:$C$15,4),$C$2:$C$15,0))







Related Video Tutorials:
See also  Lesson#20: Making an age calculator in excel with DATEDIF function

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.

Leave a Reply

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

*