Are you struggling to highlight the maximum two values in a column or row based on a specific criterion in Google Sheets? Don’t worry, I’ve got you covered! In this tutorial, I’ll show you an awesome combination of formulas that will help you achieve this efficiently.
Why the LARGE alone won’t work?
You might be wondering why the LARGE function alone won’t do the trick. Well, just like MAXIFS, Google Sheets doesn’t have a LARGEIFS function. But fear not, we can still achieve the desired result by using the IF logical function with LARGE.
Now, let’s dive into the details and learn how to conditionally format max two values based on a criterion in Google Sheets.
How to Conditionally Return Large Two Values Using Large + IF
To conditionally return the two largest values, there are various options available in Google Sheets. However, if your focus is on conditional formatting, then the combination of LARGE + IF is your best bet.
First, let me show you how to return two large values using the LARGE formula in Google Sheets, without any conditions. Then we’ll move on to using the LARGE + IF combination for conditional formatting.
Extract Large 2 Values without Conditions/Criteria in Sheets
To extract the largest two values in a range without any conditions, you can use the following formulas:
Large #1 Formula: =LARGE(C3:C8,1)
Large #2 Formula: =LARGE(C3:C8,2)
Extract Large 2 Values with Conditions/Criteria in Sheets
Now, let’s learn how to use the IF function with LARGE to incorporate conditions.
In my example, the purpose of the IF formula is to return the value in column C if the corresponding value in column B is greater than or equal to 100, otherwise it returns 0.
The formula looks like this: =ArrayFormula(IF($B$3:$B$8>=100,$C$3:$C$8,0))
You can see the output of this IF array formula in the screenshot below.
Now, let’s use this formula as the data in the LARGE function to return the two largest values based on the specified condition.
Large #1 Formula with Condition: =ArrayFormula(LARGE(IF($B$3:$B$8>=100,$C$3:$C$8,0),1))
Large #2 Formula with Condition: =ArrayFormula(LARGE(IF($B$3:$B$8>=100,$C$3:$C$8,0),2))
Awesome! Now you know how to return the two largest values based on a given criterion. But what about conditional formatting? Let’s move on to that.
Google Sheets Formulas to Conditional Format Max Two Values Based on Criterion
To highlight the maximum two values based on a specific criterion in Google Sheets, follow these steps:
- Open the “Conditional format rules” panel in Google Sheets by clicking on the “Format” menu and selecting “Conditional formatting”.
- In the panel, set the “Apply to range” to the desired range (e.g., C3:C18).
- Under “Format rules”, choose “Custom formula is” and enter the following formula:
=C3=ArrayFormula(LARGE(IF($B$3:$B$8>=100,$C$3:$C$8,0),1)). - Customize the formatting style to your liking.
- That’s it! This will conditionally highlight the maximum value in the selected range.
To highlight the second maximum value based on the condition, repeat the above steps but replace the custom formula with =C3=ArrayFormula(LARGE(IF($B$3:$B$8>=100,$C$3:$C$8,0),2)).
If you want to highlight the third maximum value or any other value, simply change the n in LARGE. For example, to highlight the third maximum value, use =C3=ArrayFormula(LARGE(IF($B$3:$B$8>=100,$C$3:$C$8,0),3)).
That’s all there is to it! With these formulas, you can easily conditional format the maximum two values based on a specific criterion in Google Sheets.
Now go ahead and give it a try. Enjoy the power of conditional formatting in Google Sheets!
Resources:
