Using Excel's LARGE Function

If you're familiar with Excel's MAX and MIN functions, you know how to find the largest and smallest values in a range. But what if you want to find the second or third largest value rather than the absolute maximum? To solve this problem, we use Excel's LARGE function.

The simple LARGE function

The syntax for the LARGE function is very simple:

=LARGE(range, k)

Given these inputs, LARGE will find the kth largest value within the specified range of values.

Let's look at a simple example of LARGE in action. SnackWorld has a list of product sales by category, and wants to know what its second-best performing category was. How can it do this using LARGE?

=LARGE(C3:C6, 2)
Output: $4,000,000

The above function outputs $4,000,000, because the 2nd largest number in the range C3:C6 is $4,000,000.

Let's try another example; say that this time, SnackWorld wants to find the third-best performing category. To modify our sheet, we just need to change the k argument to 3 from 2:

=LARGE(C3:C6, 3)
Output: $3,000,000

This formula outputs $3,000,000, because the 3rd largest value in the range C3:C6 is $3,000,000.

Using LARGE with INDEX MATCH

Of course, SnackWorld probably wants to know the name of these categories, not just the dollar sales. To get category names as well as dollar sales values, we can combine the LARGE function with INDEX MATCH, a set of lookup functions, like so:

=INDEX(B3:B6, MATCH(F2, C3:C6, 0))
Output: "Cookies"

If you aren't familiar with INDEX MATCH and the formula above is confusing, head on over to our INDEX MATCH tutorial to read up on this useful function. Come back afterwards and try to implement LARGE and INDEX MATCH together!

Now you know how to use LARGE to extract the kth largest value from a range. Questions or comments on the above tutorial? Let us know in the comments section below!

Comments