Excel SMALL and LARGE functions + Formula Examples (2024)

Want to pick out the smallest or the largest numbers from your dataset? But don’t want to reposition it?

The SMALL and LARGE functions of Excel will help you do that without reshaping your data.

In the guide below, I will walk you through both these functions using multiple examples. So don’t wait any further – and dive right in.

Download our free sample workbook here to tag along with the guide better 📌

How to use the SMALL function

The SMALL function (just like you guessed it) will extract the k-th smallest value from any given dataset 👀

It has two required arguments:

  • Array – the array of range values from where the smallest value is sought
  • K – the k-th smallest value that is sought

I know it’s always boring to keep reading about functions. So how about an example ❓

The holiday season is yet not over, and we have a list of airlines with their ticket prices mentioned next to them.

Data points for airline tickets

Let’s find the 4th cheapest ticket from the list above 🛫

To help you double-check the accuracy of the function, we have organized the data in ascending order. This is not needed to run the SMALL otherwise 🙈

Kasper Langmann, Microsoft Office Specialist
  1. Write the SMALL function as below:

= SMALL (

Writing the Excel SMALL function
  1. For the first argument (array), create a reference to the cell range containing the numeric values (from where the smallest value is sought).

We are creating a reference to the cell range B2:B8.

= SMALL (B2:B8

Creating a reference to the supplied array
  1. For argument K, write the position of the lowest value.

We want to find the 4th cheapest ticket. In other words, we seek the 4th smallest value from the dataset above.

So our function becomes:

= SMALL (B2:B8, 4)

The K value for this built in function

Pro Tip!

Be careful while you write the k argument. If the value for k is a number smaller than 1 or a number greater than the number of values in your dataset, the SMALL function will return the #NUM error or the wrong value.

For example, the data above has a total of 7 numerical values. If you write the k argument as 8 (or any number greater than 7), you’ll end up getting the #NUM error 🛑

  1. You’re only an ‘Enter’ away from the desired results. So go on and hit it.
The nth smallest value

That’s $700 by Purple Airlines. Impressive to see how the SMALL function works, isn’t it 😍

The SMALL function is available in all versions of Microsoft Excel starting from Excel 2010 to Excel 365.

Kasper Langmann, Microsoft Office Specialist

How to use the LARGE function

If you thought the LARGE function would be a dupe of the SMALL function – you made a bang-on target 🎯

The Excel LARGE function will extract the k-th largest value from any given dataset.

It also has the same two arguments:

  • Array – the array of range values from where the largest value is sought
  • K – the k-th largest value that is sought

Let’s not go any far and try using the LARGE function on the same example, as above. However, this time, we are on the hunt for the second-highest ticket price 🎫

  1. Write the LARGE function as below:

= LARGE (

Writing the LARGE function
  1. For the first argument (array), create a reference to the cell range containing the values (from where the largest value is sought).

We are again creating a reference to the cell range B2:B8.

= LARGE (B2:B8

Creating a reference to the array
  1. For argument K, write the position of the largest value.

We want to find the 2nd most expensive ticket (the 2nd largest value) from the dataset above.

So our function becomes:

= LARGE (B2:B8, 2)

The K value
  1. Hit Enter to get the results as follows:
The second-largest value

That’s $900 by Red Airlines 🔺

Both the SMALL and the LARGE functions are super simple to use. And you vouch for that too now, don’t you?

Pro Tip!

Why were the LARGE and SMALL functions needed when Excel had the MIN and the MAX functions 🤔

The MIN and the MAX functions will only return the minimum and the maximum values from a given dataset. However, you don’t want the lowest but the second, third (or any k-th) lowest or highest numeric value from the same dataset, these functions would no more help.

That’s where you’d need the LARGE and the SMALL function 💪

SMALL and LARGE formula examples

The basic application of the SMALL and the LARGE function is just that simple.

And now, we are diving into more examples for both these functions.

Example #1: SMALL and LARGE function with Dates

Did you know? You can use the SMALL and LARGE functions with dates too 📅

We will show you that here. The images below have a list of flights (all scheduled for different dates).

List of text values and dates

To see when the third latest flight is scheduled, let’s apply the LARGE function:

  1. Write the LARGE function as below:

= LARGE (B2:B8

LARGE function in Microsoft Excel

For the first argument (array), we have referred to the cell range that contains the dates.

  1. For argument K, write 3. That’s because we want to find the third latest flight.

= LARGE (B2:B8, 3)

The K value
  1. Hit Enter to get the results as follows 📫
The third largest value

That’s on 31st May 2023.

Pro Tip!

Instead of the date (31st May 2023), are you getting a serial number as result? If that’s the case, relax 🚩

There is no problem with your function. You only need to reformat the subject cell. To do that:

  • Select the cell.
  • Go to the Home Tab > Format > Short Date Format.

And everything will be sorted.

Similarly, if you want to find the third earliest flight:

  1. Write the SMALL function as follows:

= SMALL (B2:B8, 3)

The SMALL function

The function above translates as “Find the 3rd smallest date from the cell range B2 to B8″.

  1. Hit Enter to get the results.
The SMALL function returns the answer

And there you have it! On 25th March 2023✔

Example #2: With the XLOOKUP function

In the above example, we have found the third earliest and the third latest dates when the flights are scheduled.

But what if we want the answer to be different? What if you want to fetch Flight No., and not the date when that flight is scheduled 🤯

We need to combine the XLOOKUP function with the SMALL and LARGE functions to get that. So let’s do it.

  1. Write the XLOOKUP function as follows:

= XLOOKUP (

XLOOKUP function in Excel spreadsheet
  1. As the lookup_value, write in the LARGE function as written above.

= XLOOKUP ( LARGE (B2:B8, 3)

Nesting the LARGE function

We have just told Excel to look for the third largest value from the cell range B2:B8.

  1. Create a reference to the lookup range (where the lookup value is to be looked for).

The lookup value will be the third latest date, so the lookup range will be the cell range where the dates sit🥂

= XLOOKUP ( LARGE (B2:B8, 3), B2:B8

Defining the lookup array values
  1. Create a reference to the return array.

We want the flight number to be returned so, the return array will be the column for flight numbers.

= XLOOKUP ( LARGE (B2:B8, 3), B2:B8, A2:A8)

Defining the return array
  1. Hit Enter.
The third latest flight

We get Flight No. 1. Check out the date against it. It’s 31st May 2023 – the same that we got above 🏆

Let’s do the same for the third earliest flight. Here you go!

  1. Write the XLOOKUP function as follows:

= XLOOKUP ( SMALL (B2:B8, 3), B2:B8, A2:A8)

XLOOKUP and the SMALL function

Take a closer look at the function above. We have only replaced the LARGE function with the SMALL function.

And the results are here.

The third earliest flight

This time it’s Flight No. 2 (dated 25th March 2023) ✌

That’s it – Now what?

In the guide above, we learned about Excel’s SMALL and LARGE functions. So far, we have seen the syntax of both these functions along with their application (and many examples) 👌

If you found these functions useful and interesting, we have got so many more functions waiting for you in the Excel library.

Yes, you heard that right. Both these functions barely make a percent of what this giant spreadsheet program offers.

Begin mastering Excel functions from some core functions. Like the VLOOKUP, SUMIF, and IF functions of Excel.

Register for my 30-minute free email course now to get your hands on these (and many more) functions of Excel now.

Other resources

If you enjoyed reading about the SMALL and the LARGE functions of Excel, you’d also enjoy learning about other functions from the statistical function family of Excel.

Like the MAX and the MIN function. To take your learning a step ahead, read out our blog on the MAXIFS and the MINIFS function, too.

Frequently asked questions

The SMALL function of Excel returns the k-th smallest value from an array or range of values.

= SMALL (Array, k)

Array specifies the range or the array from where the smallest value is sought. Whereas k is the position of that number.

For example, if k is set to 4, Excel will return the 4th smallest value of the referred dataset.

The LARGE function of Excel returns the k-th largest value from an array or range of values.

= LARGE (Array, k)

Array specifies the range or the array from where the largest value is sought. Whereas k is the position of that number.

For example, if k is set to 4, Excel will return the 4th largest value of the referred data set.