Xtykutl

I'm trying to sort students based off frequency of participation. I have a table that is automatically generated totaling up how often a student has participated in the last few days.
I want it to do 2 things that I can't figure out.

1. I want it to ignore students that are at 0 removing them from the resulting rankings.


2. The first number is most important but I want it to reference the next value in the result of a tie.

Short example of table:

Andy - 1 1 2 3

Brad - 0 1 2 3

Cade - 1 2 3 4

Dane - 1 1 1 2

Desired result:

Cade - 1

Andy - 1

Dane - 1

The tie-breaker isn't that important and I figure I can have conditional formatting to remove children at 0, but I still can't seem to figure it out.
The closest formulas I have found in my searching are:

=INDEX($A$10:$A$9,MATCH(ROWS($C$1:C1),$C$1:$C$9,0)) 

This one doesn't work because it returns #N/A for pretty much all students who are tied.

=IFERROR(INDEX($C$1:$C$9,MATCH(SMALL(NOT($C$1:$C$9="")*IF(ISNUMBER($C$1:$C$9),COUNTIF($C$1:$C$9,"<="&$C$1:$C$9),COUNTIF($C$1:$C$9,"<="&$C$1:$C$9)+SUM(--ISNUMBER($C$1:$C$9))),ROWS($C$1:C1)+SUM(--ISBLANK($C$1:$C$9))),NOT($C$1:$C$9="")*IF(ISNUMBER($C$1:$C$9),COUNTIF($C$1:$C$9,"<="&$C$1:$C$9),COUNTIF($C$1:$C$9,"<="&$C$1:$C$9)+SUM(--ISNUMBER($C$1:$C$9))),0)),"")

I had this formula that can handle ties but it needs to be OFFSET but I don't know how since it is an array formula. Also, with both these formulas it reverses the ranks with the lowest values at the top. If anyone could assist me I would greatly appreciate it. I'm doing this so that I can give all students a chance to participate equally.

Use a helper column. In that column put the following formula:

=IF(B1=0,"n/a",SUMPRODUCT(B1:E1/10^(COLUMN(B1:E1)-MIN(COLUMN(B1:E1)))))

This will return a single number based on the rankings.

Then in your output column use:

=IFERROR(INDEX(A:A,MATCH(LARGE(F:F,ROW(1:1)),F:F,0)),"")

Then a simple VLOOKUP to return the first number:

=IF(I1<>"",VLOOKUP(I1,A:B,2,FALSE),"")