Select row with 2 conditions met

[guptamols]

Product	Cr1	Cr2
A	0.9619	2.54
B	0.8791	1190.17
C	0.8386	1567.58
D	0.7724	1.36
E	0.7508	1.53
F	0.7508	18.06
G	0.7485	1659.38
H	0.7485	1649.66
I	0.7485	1661.76
J	0.7153	1617.04

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

My Excel has 3 columns, these basically has 2 Criterions -

Cr 2 : Has to be minimum possible with threshold as 2 (i.e. cannot be less than 2)
Cr 1 : Has to be maximum amongst the minimum possible values of Cr 2.

For e.g. Above, select minimum of Cr 2 > D & E and then select Max amongst D & E - which is E
So the result is E

Have to do the same repeatedly so want some automation solution (formula etc) related to the same.

 

[steve the fish]

Hi. Could you explain that again? First thing I read is Cr 2 cannot be less than 2. Then you say the answer is E where Cr 2 is 1.53. That's less than 2 isn't it? Am I missing something?

 

[guptamols]

Hi. Could you explain that again? First thing I read is Cr 2 cannot be less than 2. Then you say the answer is E where Cr 2 is 1.53. That's less than 2 isn't it? Am I missing something?

Sorry @Steve Please read as Cr 2 should be less than 2

 

[63falcondude]

You can use Conditional Formatting to highlight the row if you'd like. Try this:

Highlight A2:C11 > Conditional Formatting > New Rule > Use a formula

=$C2=SMALL($C:$C,COUNTIF($C:$C,"<="&2))

Format however you like > OK > OK

0.7724 > 0.7508 so I don't know why column E would be highlighted. Because of this confusion, this formula does not take column B (Cr1) into account.