Excel Tip Using the LARGE function to find large values in a data array

Excel Tip Using the LARGE function to find large values in a data array: In the case of finding the largest value, we can use the MAX function. But what about the second, third, or nth largest values in a data table? This is where the LARGE function comes in. The following article will guide you on how to use the LARGE function in Excel.

Syntax of the LARGE function

Function syntax: =LARGE(array, k)

Where:

• Array: Required argument, the array or range of data in which you want to determine the kth largest value.
• K: Required argument, the position (counting from the largest) in the data array to return.

Note:

• If n is the number of data points in the array, then the LARGE(array, 1) function returns the largest value, and the LARGE(array, n) function returns the smallest value.
• If the array argument is empty, the LARGE function returns the #NUM! error value.
• If k ≤ 0 or if it is greater than the number of data points in the array, the LARGE function returns the #NUM! error value.

2. How to use the LARGE function

Let’s take an example of a revenue table for various beverage items as shown below.

The task is to find the item with the third-largest revenue.

Excel Tip Using the LARGE function to find large values in a data array

To find the item with the third-largest revenue, we need to go through 2 steps:

• Step 1: Find the third-largest revenue value using the LARGE function
• Step 2: Combine the LARGE function with the INDEX + MATCH functions to find the corresponding item name based on the third-largest revenue value

Step 1: Use the LARGE function to find the third-largest revenue

• Applying the above function syntax, we have the following formula to find the third-largest revenue:

=LARGE(C2:C10, 3)

Step 2: Combine the LARGE function with the INDEX + MATCH functions to find the item name

We have the following formula to find the item name:

=INDEX(B2:B10, MATCH(C12, C2:C10, 0))

Alternatively, we can directly include the LARGE function in the formula:

=INDEX(B2:B10, MATCH(LARGE(C2:C10, 3), C2:C10, 0))

That’s it! The above article has guided you on how to use the LARGE function in Excel. Good luck with your Excel tasks!