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