How do I reference the cell which returned a lookup value?

[tequila27]

Imagine that I've got a column of 100 numbers. First, I want to find the
maximum value (MAX function). Let's say that turns out to be in the 80th row.
Now I want to find the minimum value from the 80th through the last (100th)
row. Note that the
range of my use of the MIN function depends on where the original MAX value
came from. Any ideas on how to do this? I think what I need is a function
which can tell me which row contains the winning MAX value. Something like:

=ROW(MAX(R1C1:R100C1))

but that obviously doesn't work because the ROW functions wants a reference
- not a value.

 

[Dave Peterson]

=MIN(INDEX(A:A,MATCH(MAX(A:A),A:A,0)):A100)

(Where A100 is the last used (or after the last used cell in column A).)