INDEX/MATCH the not quite MIN

The Grim Discovery

Board Regular
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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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))

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

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:
Rich (BB code):
``````
=COUNTIFS(AF90:FH90,"<="&SMALL(AF90:FH90,J2))
``````

J5, just enter and copy down:
Rich (BB code):
``````
=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:
Rich (BB code):
``````
=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))))
``````

thanks people, appreciated

Replies
7
Views
348
Replies
5
Views
533
Replies
4
Views
256
Replies
0
Views
392
Replies
15
Views
1K

1,203,266
Messages
6,054,455
Members
444,727
Latest member
Mayank Sharma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back