How to Use LARGE IF Function in Excel (With Examples)


You can use the following formulas to perform a LARGE IF function in Excel:

Formula 1: LARGE IF with One Criteria

=LARGE(IF(A2:A16="A",C2:C16),2)

This formula finds the 2nd largest value in C2:C16 where the value in A2:A16 is equal to “A”.

Formula 2: LARGE IF with Multiple Criteria

=LARGE(IF((A2:A16="A")*(B2:B16="Guard"),C2:C16),2)

This formula finds the 2nd largest value in C2:C16 where the value in A2:A16 is equal to “A” and the value in B2:B16 is equal to “Guard”.

The following examples show how to use each formula in practice with the following dataset in Excel:

Example 1: LARGE IF with One Criteria

We can use the following formula to find the 2nd largest value in C2:C16 where the value in A2:A16 is equal to “A”:

=LARGE(IF(A2:A16="A",C2:C16),2)

The following screenshot shows how to use this formula: 

LARGE IF function in Excel with one criteria

This tells us that the 2nd largest points value among all players on team A is 14.

Example 2: LARGE IF with Multiple Criteria

We can use the following formula to find the 2nd largest value in C2:C16 where the value in A2:A16 is equal to “A” and the value in B2:B16 is equal to “Guard”:

=LARGE(IF((A2:A16="A")*(B2:B16="Guard"),C2:C16),2)

The following screenshot shows how to use this formula: 

LARGE IF Excel with multiple criteria

This tells us that the 2nd largest points value among all Guards on team A is 7.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Use a Median IF Function in Excel
How to Calculate the Mean and Standard Deviation in Excel
How to Calculate the Interquartile Range (IQR) in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *