Select row with 2 conditions met

[guptamols]

[TABLE="width: 199"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD] Cr1[/TD]
[TD] Cr2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]0.9619[/TD]
[TD="align: right"]2.54[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]0.8791[/TD]
[TD="align: right"]1190.17[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]0.8386[/TD]
[TD="align: right"]1567.58[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]0.7724[/TD]
[TD="align: right"]1.36[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]0.7508[/TD]
[TD="align: right"]1.53[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]0.7508[/TD]
[TD="align: right"]18.06[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]0.7485[/TD]
[TD="align: right"]1659.38[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]0.7485[/TD]
[TD="align: right"]1649.66[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"]0.7485[/TD]
[TD="align: right"]1661.76[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD="align: right"]0.7153[/TD]
[TD="align: right"]1617.04[/TD]
[/TR]
</tbody>[/TABLE]

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.