How to Use LARGE and SMALL Function in Excel (3 Examples)

If you want to find the 3rd most expensive smartphone from a list of smartphones, or you’re searching to solve a problem like this, you’re on the right page. Excel offers functions named LARGE and SMALL for this purpose. In this article, I’m going to share how to use LARGE function and SMALL function in Excel with suitable examples and explain them so that you may use them whenever necessary.

You can view a glimpse of what our work in this article will look like from this image:

Overview of LARGE and SMALL Function in Excel


Download Practice Workbook

You may download the Workbook and practice yourself.


3 Common Uses of LARGE and SMALL Function in Excel

In this example, we have the price list of various models of smartphones. We now want to find the k-th largest (say 3rd most expensive for example) or smallest (say 2nd least expensive for example) smartphone from this list

  • Our dataset looks like this:

Dataset for SMALL and LARGE Function


1. Basic Use of LARGE and SMALL Function in Excel

In this section, we will see how we can use the LARGE and SMALL typically. From our dataset, we will find the 3rd highest and 2nd lowest values.

Let’s start with finding the 3rd highest value.

  • Type the following formula in cell B15 and hit ENTER to get the 3rd most expensive smartphone from our list:
=LARGE(C7:C13,3)

3rd most Expensive with LARGE function

  • The output look like this:

3rd most expensive output

So, the price of the 3rd most expensive smartphone is “$794”.

  • Now, for 2nd least value, type the following formula in cell B19 to get the 2nd least expensive smartphone from our list:
=SMALL(C7:C13,2)

second least expensive with SMALL function

  • The output looks like this:

2nd least expensive

So, the price of the 2nd least expensive smartphone is “$149”.

Read More: How to Use Excel Large Function in Multiple Ranges


Similar Readings


2. Use of LARGE or SMALL Function for Compatibility with INDEX and MATCH

If we want to get the “Model Name” of the k-th most expensive smartphone, we need to use a combination of INDEX, MATCH, and LARGE functions to get it.

To know the details of INDEX and MATCH functions, you may check these linked articles.

Steps:

Let’s find out the 3rd most expensive smartphone’s “Model Name”.

For this,

  • Type the following formula in cell C16 and hit ENTER.
=INDEX(B7:B13, MATCH((LARGE(C7:C13, 3)), C7:C13, 0))

FORMULA IN CELL C16

  • The output looks like this:

Output in cell C16

So the output says that the third most expensive phone in our list is “iPhone 14 Plus”.

Formula Breakdown:

The entire formula is:

=INDEX(B7:B13, MATCH(LARGE(C7:C13, 3)), C7:C13, 0))

  • LARGE(C7:C13, 3) > Here the LARGE function is looking for the 3rd largest value in cells C7:C13
    Output: 794
  • MATCH(794, C7:C13, 0 > Here MATCH is looking for the row number among cells C7:C11 that contains the value 794
    Output: 1
  • INDEX($B$7:$B$13,1) > INDEX function takes row number 1 as input and returns the corresponding value from $B$7:$B$13.
    Output: iPhone 14 Plus
  • So the final output is:
    iPhone 14 Plus

Let’s find out the 2nd least expensive smartphone’s “Model Name”.

For this,

  • Type the following formula in cell C19 and hit ENTER.
=INDEX(B7:B13, MATCH((SMALL(C7:C13, 2)), C7:C13, 0))

FORMULA IN CELL C19

  • The output looks like this:

Output in cell C19

So the output says that the second least expensive phone in our list is “TCL 30 SE”.

The formula breakdown of this SMALL segment is quite similar as the just mentioned LARGE segment.

Read More: How to Find Largest Number in Excel (4 Quick Ways)


3. Use of LARGE and SMALL with Excel SUM Function

If we want to get the sum of, say the sum of the prices of the first two most expensive smartphones, we may use the SUM function along with the LARGE function.

Steps:

Type the following formula in cell B16 and hit ENTER :

=SUM(LARGE(B3:B10,{1,2}))

Use of LARGE function with Sum function

Here, {1,2} indicates the k value; at first k takes the value “1” and does the calculation, then it takes the value “2” and does the calculation.

  • The output of the formula looks like this:

Use of LARGE function with Sum function Output

So, the sum of the first two most expensive smartphones is “$1984”.

Now, if we want to get the summation of the prices of two least expensive phones, we need to follow these steps:

  • Type the following formula in cell B19 and hit ENTER :
=SUM(SMALL(B3:B10,{1,2}))

Use of SMALL function with Sum function

Here, {1,2} indicates the k value; at first k takes the value “1” and does the calculation, then it takes the value “2” and does the calculation.

  • The output of the formula looks like this:

Use of SMALL function with Sum function

So, the sum of the first two least expensive smartphones is “$278”

Read More: How to Use Excel LARGE Function with Criteria( 4 Suitable Ways)


Things to Remember

  • The value of k neither can exceed the number of elements in the data array nor be zero(0) or be taken from a range of negative numbers. Also, the array cannot be empty. If any of these happens, the LARGE and SMALL functions in Excel return #NUMBER! error.
  • LARGE and SMALL functions ignore the ‘Text’ type data in the array.
  • If the value of k becomes “1” by any means, then the output of the LARGE and SMALL functions will be the same as the output of the MAX and MIN functions respectively.

Conclusion

If you’re at this segment, I thank you for your interest in this content. I’ve demonstrated the details of how to use the LARGE and SMALL function in Excel. I hope you get the necessary solution. Being said that, if you face any problem regarding this article or have any queries, please feel free to leave a comment in the comment box below, Exceldemy team will try to solve that for you. Have a good day!


Related Articles

Hadi Ul Bashar

Hadi Ul Bashar

I'm Hadi Ul Bashar, an Engineering aspirant. I always look forward to keeping myself up to date in my area of interest. Currently, I'm happy to work at Exceldemy as an Excel & VBA Content Developer. My goal here is to provide an easy and detailed solution to whatever problem you may face in Excel. I hope I will make your Excel life easy as I'm always open to solve new problem!

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo