using large function with duplicates

**merlyn45** · 10-10-2012, 05:05 PM

Hello...

apple pear peach grape cherry
1 0 1 0 0

Crappy example, I know. I am trying to return the "fruit name" of the highest 3 values over zero in row 1 by using the Large function and index/match in three seperate cells. My problem is when there are duplicates. I need to return "apple" in f1, and "peach" in f2 and nothing in f3 if the rest are zero. I there is a third number greater than zero, I need that returned, or if there is only 1 greater that zero, then that name, with f2 and f3 empty. Here is my attempt:
=IF(LARGE(A2:E2,1)=0,"",INDEX($R$6:$Y$6,MATCH(LARGE(A2:E2,1),A2:E2,0))) IN F1
=IF(LARGE(A2:E2,2)=0,"",INDEX($R$6:$Y$6,MATCH(LARGE(A2:E2,2),A2:E2,0))) IN F3
=IF(LARGE(A2:E2,3)=0,"",INDEX($R$6:$Y$6,MATCH(LARGE(A2:E2,3),A2:E2,0))) IN F3
But of course..this doesnt work with duplicates.

**Blake 7** · 10-10-2012, 05:28 PM

I think this should help you.

**Blake 7** · 10-10-2012, 05:30 PM

Here's an alternative using a "helper" column taken from an old ddl post which i have stored.

Using the setup in the previous attachment, insert a new column A to the left of your names and use this formula in A1 copied down to give a unique rank

=RANK(C1,C$1:C$16)+COUNTIF(C$1:C1,C1)-1

then in G1 copied down to list the names in order

=VLOOKUP(ROWS(G$1:G1),A$1:B$16,2,0)