Feb 14 2022 03:51 AM
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%).
Feb 14 2022 04:56 AM
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)
)
Feb 14 2022 05:20 AM
Feb 14 2022 07:11 AM
@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.
Feb 14 2022 07:31 AM
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