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

T

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.
 
D

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).)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top