Use Value In An Adjacent Cell

[Mark Hatfield]

I would like to find the maximum number in a column of numbers and then use
the value in the cell one cell above and one cell to the right of the
maximum number. Can someone assist me with the formula?

The Data:

B C D
10/25/2004 57,636 19.039
11/03/2004 58,015 19.627
11/12/2004 58,431 17.774


This formula will return 58,431 (the maximum number in the range)

=MAX($C$6:$C$55)

Once I find that number I need the value one cell above and one cell to the
right (19.627 in this case).

Thanks,

Mark

 

[JE McGimpsey]

One way:

=INDEX(D,MATCH(MAX(C:C),C:C,FALSE)-1)

 

[Frank Kabel]

Hi
one way:
=INDEX($D$6:$D$55,MATCH(MAX($C$6:$C$55),$C$6:$C$55,0)-1)