INDEX/MATCH the not quite MIN

[The Grim Discovery]

Hiya

I'm using this formula to index/match the worst perfoming candidate across a series of criteria:

=INDEX(AF2:FH2,MATCH(MIN(AF90:FH90),AF90:FH90,0))
**The row 2 in the formula contains the candidates names which the formula returns while the row 90 is an aggregated average of their scores.***

It works a treat. But how do I adjust it to identify the worst three. I had supposed it would be to adjust the very last digit of the formula from a '0' to a '1' and a '2' respectively. But this returns a n/a error. Help would be brilliant. (Or if further clarity is needed give me a shout.)

Ta
Lord Grimsworthy

 

[RoryA]

Instead of using MIN, use SMALL and pass 1, 2, or 3 as the last argument:

=INDEX(AF2:FH2,MATCH(SMALL(AF90:FH90,1),AF90:FH90,0))

 

[Rada1]

Or if you want a list of the worst three, try this

=IF(ROWS(A$1:A1)>3,"",INDEX($AF$2:$FH$2,MATCH(SMALL($AF$90:$FH$90,ROWS(A$1:A1)),$AF$90:$FH$90,0)))

You can adjust number incrementor "ROWS(A$1:A1)" the way you want...

 

[Aladin Akyurek]

Taking ties into account, which do occur more often than not in situtations as yours...

Row\Col

AF​

AG​

AH​

AI​

AJ​

AK​

AL​

AM​

AN​

AO​

AP​

AQ​

AR​

2​

jon

damon

kaan

linda

nathan

tolga

conrad

laurent

dan

brian

harlan

90​

25​

30​

25​

80​

75​

65​

45​

30​

75​

30​

70​

Row\Col	J​	K​
2​	3​
3​	5​
4​	smallest scores	smallest scorers
5​	25​	jon
6​	25​	kaan
7​	30​	damon
8​	30​	laurent
9​	30​	brian
10​

<tbody>
</tbody>

J3, just enter:

=COUNTIFS(AF90:FH90,"<="&SMALL(AF90:FH90,J2))

J5, just enter and copy down:

=IF(ROWS(J$5:J5)<=$J$3,SMALL($AF$90:$FH$90,ROWS(J$5:J5)),"")

K5, control+shift+enter, not just enter, and copy down:

=IF(J5="","",INDEX($AF$2:$FH$2,SMALL(IF($AF$90:$FH$90=$J5,
   COLUMN($AF$90:$FH$90)-COLUMN($AF$90)+1),COUNTIFS(J$5:J5,J5))))

 

[The Grim Discovery]

thanks people, appreciated