New Contributor

# Excel Problem on Duplicate values

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%).

4 Replies

# Re: Excel Problem on Duplicate values

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)
)``````

# Re: Excel Problem on Duplicate values

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.

# Re: Excel Problem on Duplicate values

@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.

# Re: Excel Problem on Duplicate values

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