Fetching what is associated with the Max (or Min) value is a special case of
Top N problems like building a list of 5 cheapest suppliers. If the 5th
cheapest value is offered by more than 1 supplier, it is obvious that all of
them should be included in the list, not just one. Again, suppose that Bob
and Alice have B on a Math exam and B is the Max grade. Alice wouldn't be
happy to see that just Bob has been listed in a Top 1 list. Niek Otten in
his reply, as I construe it, is alluding to this issue. I published a few
times here and elsewhere a formula system that addresses the Top N problem.
Let C2
10 house the following sample, including the labels (a smaller
sample size than yours, but that's inessential)...
{"Name","Score";"Damon",33;"Bob",37;"Brian",33;"Jack",28;"Mike",35;"Felicia"
,28;"Alice",37;"Gaz",32}
In E2 enter: Max-Rank (which is just a label).
In E3 enter & copy down:
=RANK(D3,$D$3:$D$10)+COUNTIF($D$3
3,D3)-1
which produces a ranking in descending order.
In F2 enter: Min-Rank (which is just a label).
In F3 enter & copy down:
=RANK(D3,$D$3:$D$10,1)+COUNTIF($D$3
3,D3)-1
which produces a ranking in ascending order.
In G2 enter: Max (just a label).
In H2 enter:
=MAX(D3
10)
In G3...
=MAX(IF(INDEX(D3
10,MATCH(H3,E3:E10,0))=D3
10,E3:E10))-H3
which must be confirmed with control+shift+enter instead of just enter.
This formula computes how many values are tied with the Max value (put a bit
precisely: with the Top Nth value).
In H3 enter: 1 (indicates that N = 1).
In G4 enter: Names for Max (just a label).
In G5 enter & copy down:
=IF(ROW()-ROW($G$5)+1<=$H$3+$G$3,INDEX($C$3:$C$10,MATCH(ROW()-ROW($G$5)+1,$E
$3:$E$10,0)),"")
which lists the names associated with the Max value.
Note that the ROW($G$5) bit anchors the formula to the first cell the
formula is entered.
In I2 enter: Min
In J2 enter:
=MIN(D3
10)
In I3...
=MAX(IF(INDEX(D3
10,MATCH(J3,E3:E10,0))=D3
10,E3:E10))-J3
which must be confirmed with control+shift+enter instead of just enter.
In J3 enter: 1
In I4 enter: Names for Min
In I5 enter & copy down:
=IF(ROW()-ROW($G$5)+1<=$I$3+$J$3,INDEX($C$3:$C$10,MATCH(ROW()-ROW($G$5)+1,$F
$3:$F$10,0)),"")
which lists the names associated with the Min value.
The entire data and processing area would now look like this:
{"Name","Score","Max-Rank","Min-Rank","Max",37,"Min",28;
"Damon",33,4,4,1,1,1,1;"Bob",37,1,7,"Names for Max","","Names for Min","";
"Brian",33,5,5,"Bob","","Jack","";
"Jack",28,7,1,"Alice","","Felicia","";
"Mike",35,3,6,"","","","";
"Felicia",28,8,2,"","","","";
"Alice",37,2,8,"","","","";
"Gaz",32,6,3,"","","",""}
If you'd like to receive the workbook showing the above system, just drop an
e-mail.
