View Single Post
  #1   Report Post  
Mr. Snrub
 
Posts: n/a
Default How do I find the cell address of the 2nd largest of a set?

I have a table of integers ranging from cells B3 to Z51, and I want to find
the cell address of the second-largest and third-largest value.

=LARGE(B3:Z51, 2) will give me the second-largest value, but how do I find
the cell address where that value is located?

Also, when there is the exact same value in two different cells, I want to
be able to find both cell addresses. For example, LARGE(B3:Z51, 3) will find
the third-largest value, and LARGE(B3:Z51, 4) will find the fourth-largest
value. If those two values are the same, how do I find their corresponding
addresses?

See, what I'm doing with this data is the ten highest values will be put in
another worksheet as a sort of "top ten list".

The format looks a little something like this:

Date.......Alex.........Becky......Carl........Don
1/1/05.....23...........47..........-29.........21
1/8/05.....-2...........16...........30..........-20 (etc) ...
2/2/05.....30..........-50..........40...........65
2/14/05...14..........-20..........15...........34
....

It goes on like that for a long time. With my Top Ten List, I want to have
the value along with the date and the person's name, and the only way I can
find the corresponding date or person's name is if I know the cell address.

Please help me!

Senor Snrub