How to Use Excel Large Function in Multiple Ranges

Get FREE Advanced Excel Exercises with Solutions!

You can implement an Excel statistical function while using a dataset. At times, you need to know the largest value in multiple ranges. Then, we need the LARGE function. In this article, you will learn how to use the Excel LARGE function in multiple ranges.

Overview Image


Introduction to LARGE Function

Objective

The objective of the LARGE function is to return the K-th value in a data set.

Syntax

=LARGE(array,k)

Syntax of Excel Large Function

Arguments Explanation

ARGUMENT REQUIREMENT/OPTIONAL DESCRIPTION
array Required The array from which we need to find the Kth largest value
k Required An integer that indicates the position from the largest value, as the Nth position

Version

  • The LARGE function is available from Microsoft Office 2007.
  •  Here, we will use Microsoft Office 365.

Read More: How to Use VBA Large Function in Excel


How to Use Excel Large Function in Multiple Ranges: 2 Example

We have shown 2 examples where we used the LARGE function in multiple ranges. Like, in the first example, we will use the LARGE function to get the highest value for section A. Then, for the second example, we will combine the LARGE function with other functions such as INDEX and MATCH functions. By merging other functions with the LARGE function, we can solve different types of problems more efficiently.


Example 1: Using LARGE Function to Find the Highest Value from Multiple Ranges

Here we have a dataset that represents the marks of two sections (Section A and Section B) of class 6. If we want to find the highest marks in Section A, we have to apply the formula for multiple ranges.

Sample dataset of Using Excel LARGE function in multiple ranges

Steps:

  • Write down the formula in cell D5. You must select the ranges among which you want the largest marks. In this case, we have selected the ranges of section A only.
=LARGE((C5:C6,C10,C13:C14),1)

Applying formula to use excel LARGE function in multiple ranges

  • Press ENTER to see the result.
  • You will get the highest mark in section A, which is 88.

Result of Using excel LARGE function in multiple ranges

Formula Breakdown

  • LARGE((C5:C6,C10,C12:C13),1): Here, among array (C5:C6,C10,C12:C13) the LARGE function finds the highest value (K=1).

Read More: How to Find Largest Number in Excel


Example 2: Merging INDEX, MATCH, and LARGE Functions to Get the Nth Largest Values

If we want to know the top 3 scores with the student’s name, we can merge the INDEX, MATCH, and LARGE functions.

Sample Data Set for Getting Nth Largest Values

Steps:

  • Write the formula in cell F17.
=INDEX($F$5:$F$14,MATCH(LARGE($F$5:$F$14,$E17),$F$5:$F$14,0))

Formula of Getting Nth Largest Value Using Large Function in Excel for Multiple Ranges

  • Now, hit the ENTER key.
  • Here we have the highest mark 96 as a result.

Result of Nth Largest Value Using Large Function

  • Copy it down to cell F19.
  • Thus, we have determined the top three marks.

Result of  Nth Largest Values Using Large Function

Formula Breakdown

  • LARGE($F$5:$F$14,$E17): This portion finds the highest marks (E17= 1) in the range F5:F14.
  • MATCH(LARGE($F$5:$F$14,$E17),$F$5:$F$14,0): Afterward, this portion provides the row number of the top scorer in the range F5:F14.
  • INDEX($F$5:$F$14,MATCH(LARGE($F$5:$F$14,$E17),$F$5:$F$14,0)): Finally, the INDEX function will return the marks from range F5:F14.

 

  • To get the student’s name, enter the formula in cell G17.
=INDEX($E$5:$E$14,MATCH(LARGE($F$5:$F$14,$E17),$F$5:$F$14,0))

Formula of Getting Nth Largest Value Using Large Function

  • After that, click ENTER.
  • We will get the name of the top scorer.

Result of Nth Largest Value Using Large Function in Excel for Multiple Ranges

  • Copy the formula down to cell G19.
  • We will find the names of the top three scorers.

Result of Nth Largest Value Using Large Function in Excel

 Formula Breakdown

  • LARGE($F$5:$F$14,$E17): This portion finds the highest marks (E17= 1) in the range F5:F14.
  • MATCH(LARGE($F$5:$F$14,$E17),$F$5:$F$14,0): Then, this portion provides the row number of the top scorer in the range F5:F14.
  • INDEX($E$5:$E$14,MATCH(LARGE($F$5:$F$14,$E17),$F$5:$F$14,0)): Lastly, the INDEX function will return the associated data with the highest value from the range F5:F14.

Read More: How to Use Excel LARGE Function with Criteria


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

In these two examples, I have shown multiple criteria for using the Excel LARGE function in multiple ranges. There may be some different situations where the formula needs to be modified. If you have any questions regarding this topic, please comment so that we can help.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahfuza Anika Era
Mahfuza Anika Era

Hello! Welcome to my Profile. Recently I have been graduated from Bangladesh University of Engineering and Technology in Civil Engineering. Being a fresh graduate, I want to build up my skill in article writing about Microsoft Excel and VBA. I am also interested in research and development. I believe in learning something new every day and implementing my knowledge more effectively.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo