LOOKUP from a table when your lookup value has a duplicate

[badodok]

Hi,

I think this question would have a simple solution but I can not seem to find the answer so I will try to post it instead. I have a table that has 77 names on the first column and their scores on the second column. I created another table using the LARGE function to find the top 10 scores out of the 77 entries. The problem is that I can not show the owners of the top 10 scores in the second table that I created because there is a duplicate in the scores. I used INDEX and MATCH and the duplicate scores would show 1 name as it would only lookup the first value the function sees from the table.

Thanks in advance! Cheers!

 

[ww4612]

why not use sorting function to sort the table based on socre column in decending order without use any formular?

 

[XOR LX]

Or you could use this:

=INDEX($A$1:$A$77,MATCH(LARGE(INDEX($B$1:$B$77+ROW($B$1:$B$77)/10^9,,),ROWS($1:1)),INDEX($B$1:$B$77+ROW($B$1:$B$77)/10^9,,),0))

based on names in A1:A77 and scores in B1:B77.

Copy down as required.

Regards

 

[badodok]

Wow! Thank you so much for taking the time to answer this. I knew it would be possible with a formula, I just couldn't think of how. Temporarily, I added another column with numbers starting from 0.0001 and then dragged it down, used CONCATENATE to join it with column A since I only need to show whole numbers in the other table. "+ROW($B$1:$B$77)/10^9" was clever. Again, thanks a lot!