Excel Problem on Duplicate values

[Zellie1704]

Good day,

I want to obtain the top 3 highest scores in the table below without duplications. In this example, the correct order should be Numeric Skills (B18) - 100%; Microsoft Word (B25) - 100%; Excel Skills (B24) - 96%. The same goes for the lowest scores obtained in the test, in other words: Legal Aspects (40%); Managing a small business (50%) and Cost Accounting (60%).

[#2]

Hi @Zellie1704 

One way with Excel Web/2021/365

in E3

=LET(
    ScoresDesc, SORT(UNIQUE(Score),,-1),
    XLOOKUP( INDEX(ScoresDesc,SEQUENCE(MIN(ROWS(ScoresDesc),3))), Score, Skills,, 0)
)

in F3

=LET(
    ScoresAsc, SORT(UNIQUE(Score)),
    XLOOKUP( INDEX(ScoresAsc,SEQUENCE(MIN(ROWS(ScoresAsc),3))), Score, Skills,, 0)
)

[#3]

Hi L.z,

Thank you for your input, is it possible to explain how the the LET function / formula works? I get an Alert message from excel that the first argument of LET must be a valid name? Also please note I'm using a Mac, which means I need to use ; and not , Not sure if this is a new to Excel or only applicable to Mac users.

[#4]

@Zellie1704 The semi-colon is not Mac specific. It depends on your local settings. That aside, have a look at the attached file. It should "translate" into your local format automatically. And I believe that these formulae do what you ask for. They allow for duplicate scores but they will not duplicate descriptions descriptions.

[#5]

@Zellie1704 

As @Riny_van_Eekelen mentioned this isn't Mac specific. You probably get the message "The first argument of LET must be a valid name" because your formula separator (based on your regional settings) isn't the comma but probably ;. So replace all , with ; in the suggested formulas and this should work

Re. LET. It's not mandatory as the following formula for the Highest would work as well

=XLOOKUP(INDEX(SORT(UNIQUE(Score),,-1),SEQUENCE(MIN(ROWS(UNIQUE(Score)),3))),Score,Skills,,0)

However, notice that UNIQUE(Score) is done (calc.) twice in the above formula. Using LET we avoid doing twice the same thing