Return smallest number with criteria

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

EXCEL FORMULA 1. Return smallest number with criteria with SMALL and IF functions

EXCEL

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

{=SMALL(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 smallest number.

GENERIC FORMULA

{=SMALL(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 smallest number.

EXPLANATION

This is an array formula that uses the SMALL and IF functions to return the smallest 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 smallest 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 SMALL function to return the smallest 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
Return largest number with criteria How to get the largest number from a list with a specific criteria
Lookup nth smallest value with criteria How to lookup the nth smallest value in a range with criteria
Lookup nth smallest value How to lookup the nth smallest value in a range

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
SMALL Function The Excel SMALL function returns the numeric value from a specified range based on the nth smallest 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