Return largest number with criteria

This tutorial shows how to get the largest number from a list with a specific criteria using the LARGE and IF functions

EXCEL FORMULA 1. Return largest number with criteria with LARGE and IF functions

EXCEL

Hard coded formula
Return largest number with criteria
Cell reference formula
Return largest number with criteria
{=LARGE(IF(B7:B11="Team A",C7:C11),1)}
{=LARGE(IF(B7:B11=C4,C7:C11),1)}
GENERIC FORMULA

{=LARGE(IF(criteria_rng="criteria",value_rng),1)}

ARGUMENTS
criteria_rng: A range that contains the values associated with the criteria.
criteria: The criteria that you want to set.
value_rng: The numbers that are associated with the criteria range from which you want to extract the largest number.

GENERIC FORMULA

{=LARGE(IF(criteria_rng=criteria,value_rng),1)}

ARGUMENTS
criteria_rng: A range that contains the values associated with the criteria.
criteria: The criteria that you want to set.
value_rng: The numbers that are associated with the criteria range from which you want to extract the largest number.

EXPLANATION

This is an array formula that uses the LARGE and IF functions to return the largest number with a specific criteria.
The IF function is used to identify the values that meet the criteria and the associated numbers from which we want to return the largest number. Now that the IF function has isolated the numbers that are associated with the criteria range that meet the specific criteria it uses the LARGE function to return the largest number from this list of filtered numbers for the specific criteria.
Please note that this is an array formula and once you have entered the formula you will need to press the control + shift + enter keys simultaneously, which will convert the formula into an array formula and enclose the it in { }.

Click on either the Hard Coded or Cell Reference button to view the formula that has the criteria directly entered into the formula or referenced to a specific cell.

RELATED TOPICS

Related Topic Description Related Topic and Description
Lookup nth largest value with criteria How to lookup the nth largest value in a range with criteria
Lookup nth largest value How to lookup the nth largest value in a range

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
LARGE Function The Excel LARGE function returns the numeric value from a specified range based on the nth largest position
IF Function The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE