Finding top values with criteria-CodePudding

I am trying to extract a sorted list of the top 15 technologies from my data sheet in category 1. I have a data sheet with the below structure:

Technologies   Category  Score
  Tech 1          1       35
  Tech 2          3       59
  Tech 3          5       26
  Tech 4          3       76
  Tech 5          1       28
  Tech 6          1       0
  Tech 7          1       35
    ...          ...      ...

I have tried Index Match with Large & IF, but it didn’t give the right result; I got duplicate technology results, and I figured out that because I have duplicate scores, the result I get is Tech 1, Tech 1, Tech 5 which rather should be Tech 1, Tech 7, Tech 5. I also tried Sort, Filter combination with Large, which didn’t work, I get Value error with it:

=SORT(FILTER('Data Sheet'!G2:G126;('Data Sheet'!L2:L126>=LARGE(IF('Data Sheet'!K2:K126=1;'Data Sheet'!L$2:L126);15))*('Data Sheet'!K2:K126=1));15;-1)

I also would like to exclude if I reach values 0 in my top 15. Any suggestions how to fix the formula and include this additional aspect?

CodePudding user response：

Does this formula fit your needs:

=TAKE(SORT(FILTER(tblData,(tblData[Category]=1)*(tblData[Score]<>0)),3,-1),15)

You will need the current channel of Excel 365 - due to the TAKE function

UPDATE: Using your setup you will need this formula:

= TAKE(SORT(FILTER('Data Sheet'!G2:L41,('Data Sheet'!K2:K41=1)*('Data Sheet'!L2:L41<>0)),6,-1),15,1)