excel - Get Top Performer by Subgroup Using Index and Match -

i trying rank names in column c largest smallest score.

category    score   name        total rank  apple rank  orange rank apple   10  joe     rachel  rachel  0 orange  15  don     natalie 0   natalie apple   20  james       tom tom 0 apple   1   rob     nothing nothing 0 orange  3   mary        gina    0   gina orange  100 rachel      james   0   james orange  99  natalie     don 0   don orange  87  tom     joe 0   joe apple   27  gina        mary    mary    0 orange  30  nothing     rob 0   rob 

this works in column e apples , oranges, formula in e2

=index($c$2:$c$25,match(1,index(($b$2:$b$25=large($b$2:$b$25,rows(e$1:e1)))*(countif(e$1:e1,$c$2:$c$25)=0),),0)) 

however, goal compare apples apples , oranges oranges. only, formulas in columns f , g show "0" values rows aren't in right apple/orange category.

for f2:

=if($a:$a="apple",index($c:$c,match(1,index(($b:$b=large($b:$b,rows(f$1:f1)))*(countif(f$1:f1,$c:$c)=0),),0)),0)

for g2:

=if($a:$a="orange",index($c:$c,match(1,index(($b:$b=large($b:$b,rows(g$1:g1)))*(countif(g$1:g1,$c:$c)=0),),0)),0)

how modify codes 0 values won't show up? great: (screenshot made copy pasting values...)

apple rank  orange rank rachel  natalie tom gina nothing james mary    don     joe     rob 

note: unless whole column ranges required steps below may seem take uncomfortably long time if these ranges not restricted.

assuming have below in columna:g , corresponding layout:

then columnsi:j may achieved quite copying columnf:g , paste special..., values i1, select columnsi:j, home > editing - find & select, replace..., find what: 0, replace with: , replace followed find & select, go special..., select blanks (only), ok, right-click on 1 of chosen cells , delete..., shift cells up, ok.

to remove 0s columnf:g replacing final 0 in each formula "" sufficient.