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

[Aitch]

A	B	C	D
2		1
19		2
7		3
12		4
1		5
16		6	Target
3		7
0		8

<tbody>
</tbody>

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