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:

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:

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:

• The output look like this:

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:

• The output looks like this:

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

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

Similar Readings

• How to Use VBA Large Function in Excel (4 Suitable Examples)
• [Fixed!] Sort Largest to Smallest Not Working in Excel
• How to Find Second Largest Value with Criteria In Excel

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.

• The output looks like this:

So the output says that the third most expensive phone in our list 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.

• The output looks like this:

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 :

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:

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 :

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:

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

• How to Use Excel Large Function with Text (3 Easy Ways)
• Lookup Next Largest Value in Excel (4 Easy Ways)
• How to Find Largest Lookup Value in Excel (3 Easy Ways)