Looking for Max value from the bottom of a column...

[Aitch]

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Target[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Kinda backwards!

How do I create a formula to paste down column D, which highlights the first value over 10 in column A - but coming up from the bottom of column C

I'm trying to use LARGE and MAX using column C - but it highlights all the values above 10

How do I make sure it's only one value in the range and the first one from the bottom?

Thank you for helping!

 

[lrobbo314]

Not sure about a formula to copy down, but you can put this formula in a single cell, then use that value as a conditional formatting rule.

=INDEX(A1:A8,MAX((A1:A8>10)*C1:C8))

This is an array formula, so Ctl+Shift+Enter.

 

[Fluff]

How about
=IF(LOOKUP(2,1/($A$1:$A$8>10),$A$1:$A$8)=A1,"Target","")
But if you have two or more cells containing 16, this will highlight all of them

 

[Marcelo Branco]

Maybe...

D2 copied down
=IF(AND(A2>10,COUNTIF(A2:A$9,">10")=1),"Target","")

M.

 

[Aitch]

Perfect, Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback