< 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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment