You can use the following methods to create a LARGE IF formula in Google Sheets:
Method 1: LARGE IF with One Criteria
=ArrayFormula(LARGE(IF(A2:A11="value",C2:C11),2))
This formula finds the second largest value in column C where the value in column A is equal to “value.”
Method 2: LARGE IF with Multiple Criteria
=ArrayFormula(LARGE(IF((A2:A11="value1")*(B2:B11="value2")=1,C2:C11),5))
This formula finds the fifth largest value in column C where the value in column A is equal to “value1” and the value in column B is equal to “value2.”
The following examples show how to use each method with the following dataset in in Google Sheets:
Example 1: LARGE IF with One Criteria
We can use the following formula to calculate the second largest value in the Points column only for the rows where the Team column is equal to “Spurs”:
=ArrayFormula(LARGE(IF(A2:A11="Spurs",C2:C11),2))
The following screenshot shows how to use this syntax in practice:
We can see that the second largest Points value among rows where Team is equal to “Spurs” is 26.
Example 2: LARGE IF with Multiple Criteria
We can use the following formula to calculate the second largest value in the Points column only for the rows where the Team column is equal to “Spurs” and the Position column is equal to “Guard”:
=ArrayFormula(LARGE(IF((A2:A11="Spurs")*(B2:B11="Guard")=1,C2:C11),2))
The following screenshot shows how to use this syntax in practice:
We can see that the second largest Points value among rows where Team is equal to “Spurs” and Position is equal to “Guard” is 24.
Note: You can find the complete documentation for the LARGE function in Google Sheets here.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: How to Multiply Column by a Constant
Google Sheets: How to Multiply Column by a Percentage
Google Sheets: How to Rank Items by Multiple Columns
