CODEWITHSUNDEEP
excelmatchoffsetexcel-2016
Michael Clarke
Michael Clarke

Reputation: 169

Excel offset/match avoid duplicate

I have 2 columns, B2-B21 have titles, C2-C21 have values.

In E2-E6 I am using Large to find the 5 largest values in the C2-C21 range. In D2-D6 I am using =OFFSET(INDEX($B$2:$B$21,MATCH($C2,$B$2:$B$21,0)), 0, -1)

To get me the associated title. However, because their could be 2 or 3 items with the same count in E2-E6 it currently gives me the same name for both if they are duplicate.

Is the a mix between large/offset that I could utilize? I tried the below but this fell into the same trap of returning the duplicate title.

=OFFSET(INDEX($B$2:$B$21,MATCH(LARGE(B3:B21, 1),$B$2:$B$21,0)), 0, -1)

Upvotes: 0

Views: 2064

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

enter image description here

Formulas:

E2 downwards:

=LARGE($C$2:$C$21,ROW(A1))

F2 downwards:

{=INDEX($B$1:$B$21,--RIGHT(SMALL(RANK.EQ($C$2:$C$21,$C$2:$C$21,0)*10^5+ROW($C$2:$C$21),ROWS($1:1)),5))}

The formula in F2 is an array formula. Input it into the cell without the curly brackets. Then press [Ctrl]+[Shift]+[Enter] to confirm. The curly brackets will then appear automatically.

Upvotes: 1

Related Questions