How to Use a LARGE IF Formula in Google Sheets

By /

A LARGE IF Formula in Google Sheets is a function that allows you to compare two or more values and return the largest value. It can be used to compare values across columns, rows, or even multiple sheets. To use the LARGE IF Formula, you must enter the range of cells to compare, followed by the criteria that will be used to determine which value is the largest. You can then enter the value that you want the formula to return for the largest result. The LARGE IF Formula is a useful tool for finding the largest value out of a range of values.

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:

LARGE IF formula in Google Sheets

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 .

x

Related terms:

  1. How do I check that an element of a large vector meets multiple conditions?
  2. Fix error in plot.new() : figure margins too large?
  3. Google Sheets: A Formula for LEFT Until Specific Character
  4. ## Google Sheets: Weighted Average IF Formula
  5. How can I use a Concatenate If Formula in Google Sheets?
  6. How to Use a RANK IF Formula in Google Sheets?
  7. How to Use a SUMPRODUCT IF Formula in Google Sheets
  8. What is a simple formula for “If Not Empty” in Google Sheets?
  9. How can I use a reference to a cell in a Google Sheets Query formula?
  10. What is a simple formula for “If Contains” in Google sheets?