Go Back

Large with criteria

The Excel formula Large with Criteria is the one that returns the k-th or nth largest value of the data set. This function is used to select the value of its relative standing function.

What is Excel formula: Large with Criteria?

The Large function of Excel returns the largest numeric values according to the position in the list. The function can tell the nth largest value, 2nd largest value, etc.

How the Excel formula: Large with Criteria is used in Excel?

The formula Large with Criteria is used with IF function to work properly. As we are trying to search the value with a Criteria so we have to provide criteria for the function. The Criteria is specified with the IF function.

Formula or Syntax

The syntax of the LARGE function is LARGE (array, n) but for the operation Large with Criteria, we will sue the formula as:

LARGE (IF (Criteria, values), n)

Parameters or Arguments of Excel formula: Large with Criteria function

The arguments that will be passed for the function are:

  •         Criteria: Required. In this field, you will have to provide the value or logical operation that you want to evaluate.
  •         Values: The value to be returned if the Criteria is valid.
  •         N: Required. It is the integer value that specifies the position from the largest value, i.e. nth position.


Let us consider an example of this function

  1. Make a table of the name and the score of the kids in some test. As you have entered the details of all the kids, you must find the top scorers.

Figure 1. Make a table of different Criteria

  1. As the LARGE function will only calculate the largest value, we will provide a Criteria with IF function in the field where we want the result to be displayed. As we want only the values of group B, we use the IF function to filter the value. The values in the array of “A” group will become false according to the Criteria specified.
  2.  So, we will have to pass the function with an array of the values as LARGE ({FALSE; 98; FALSE; 60; FALSE; 95; FALSE}, 2) to find the 2nd largest value in the Group B. the result will be displayed as shown in the figure.

Figure 2. Insert the LARGE with Criteria function to find 2nd largest value  

You can also use multiple Criteria with the LARGE function as.LARGE (IF ((Criteria1)*(Criteria2), values), n)

Note on Usage of Excel formula: Large with Criteria function

The notes on usage of the function are given below:

  •         The LARGE function retrieves the numeric value from a list when it is sorted by value.
  •         The IF function can be used to evaluate certain conditions depending on the result.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

i need to use sumifs for multiple criteria and a date range for a very large dataset
Solved by Z. U. in 28 mins
I use the database functions a lot for large tables of data. It's a real pain with the way the criteria works as its generally 2 rows. Is there a way around having to add a space row after each d formula to deal with criteria.
Solved by G. D. in 19 mins
need an example of Average(large) and ifs combined, my formula isn't using the criteria answer to average(large), it's averaging the entire data once it was TRUE TRUE help please
Solved by V. J. in 28 mins

Leave a Comment