**How to Use the Excel Functions**

SMALL and LARGE

SMALL and LARGE

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

With some basic sorting, it’s easy to find the smallest and largest numbers in a spreadsheet.

But sometimes you need to *put those numbers into a formula* – and that can be tricky.

That’s where the functions **SMALL and LARGE** can help you out.

They’ll pull the smallest and largest numbers from a range—and they can help you find the second smallest, third largest, and so on.

Let’s take a look at how they work, and then check out an example of how they can help you out.

**The SMALL function**

We’ll start by finding the smallest number in a range. Open up the example workbook, and you’ll see a spreadsheet with a list of sales figures for four different regions.

Using the SMALL function, we’ll find the lowest value of sales in the list.

First, though, let’s take a look at the syntax of the function.

**The syntax of the SMALL function**

**=SMALL(range, n)**

The **range** is the group of numbers from which you want to find the smallest value.

**n** tells Excel to return the nth-smallest value. So if n is 3, you’ll get the third-smallest value. If you want the smallest value, enter 1.

Click into cell F3 and type the following formula:

**=SMALL(B2:B51, 1)**

We want the smallest number, so we’ll use an n value of 1.

Hit **Enter**, and you’ll get the smallest value in the list:

If you sort the list of sales figures, you’ll see that the function correctly returned the smallest sales number.

Try finding the value for the next cell down,

5th least sales.

**The LARGE function**

LARGE is almost exactly the same as SMALL. It takes the same arguments, and returns the nth-most value.

Here’s the syntax:

**The syntax of the LARGE function**

**=LARGE(range, n)**

**range**, again, is the selection of numbers from which you want to find the largesvalue.

And **n** tells Excel which number you want. Setting n to 4 will give you the fourth-largest number. Setting it to 1 will give you the largest number.

In cell F5, we’ll type the following formula:

**=LARGE(B2:B51, 1)**

And, as expected, it returns the largest value in the Sales column:

Use what you’ve learned to fill out the next cell,

3rd most sales.

**Combining SMALL and LARGE with other functions**

Like many functions, SMALL and LARGE are the most useful when you combine them with other functions.

We’ll use them in conjunction with INDEX and MATCH to find out which regions have the most and least sales.

Here’s the formula we’ll use for getting the region with the least sales:

**=INDEX(A2:A51, MATCH((SMALL(B2:B51, 1)), B2:B51, 0))**

Let’s break that down one step at a time, starting with SMALL.

As we saw earlier, **SMALL(B2:B51, 1) **gives us the smallest number in the sales column.

Moving out one step, we have **MATCH(SMALL(B2:B51, 1), B2:B51, 0)**.

MATCH returns the location in the range of the result of our SMALL function. In short, it tells us **which row the smallest sales number is in**.

Finally, we have INDEX. This function takes the row number from MATCH and looks it up in a different column—in this case, the Region column.

So **=INDEX(A2:A51, MATCH((SMALL(B2:B51, 1), B2:B51, 0))** finds the row number of the smallest sales number in column B and returns the corresponding value from column A.

Hit **Enter** on that formula and let’s see what happens.

The function returns “West.” A quick look at the lowest number in the sales column shows that this is correct.

Again, feel free to sort the column to make sure this is true.

Try writing a function that shows you the region that posted the second-highest sales number in the report.

**More powerful than sorting**

It’s easy to find the smallest and largest values in a list with sorting.

But when you need to use those numbers in a function—or the values in the list might change regularly—the **SMALL and LARGE functions will serve you better**.

They may not seem like much at first. But after you’ve seen what they can do with other functions, you’ll understand that they’re really convenient to have around!