Match Index with Multiple results found and retrieve unique value

[Lres81715]

I'm doing a Top 10 list based on numbers and I'll be using this formula for multiple sheets.

To simplify things I have list of Top 10 numbers retrieved from table of information. This is how it currently looks

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]States[/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Top 10 State[/TD]
[TD]Top 10 Count[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]17[/TD]
[TD][/TD]
[TD]California[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Florida[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]Florida[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]New York[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Texas[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Illinois[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Maryland[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]Maryland[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]North Carolina[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Georgia[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Ohio[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Georgia[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Maryland[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Georgia[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Georgia[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Virginia[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Massachusetts[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The larger list on the left changes constantly and I want a formula in D2:D11 to automatically reflect the changes from the Top 10 Count. The second Maryland (in Red) should read Michigan and the 3 Georgia's should read North Carolina, Virginia and Massachusetts accordingly.

The normal INDEX MATCH doesn't work for me in this case because the Top 10 Count often has Doubles and triplicate numbers.

A google and MrExcel search gives me lots of results for a STATIC number (AKA lookup value or 4 or 3) but not for a dynamic list like what I need.

I attempted to tackle the array formula needed to retrieve the states. but no such luck
=INDEX($A$2:$A$55,SMALL(IF($B$2:$B$55=$E2,ROW($A$2:$A$55)-ROW($B$2)+1),ROWS($D$2:D3)))

 

[goalman14]

This is what I would do. Assuming that the text States is in A1. Use this formula =RANK(B2, $B$2:$B$14, 0)+COUNTIFS($B$2:B2,B2)-1 to rank the scores. The countifs will give each value a unique number to break the ties. Then use the following two formulas to get state and count =INDEX($A$2:$A$14, MATCH(ROWS($A$1:A1), $C$2:$C$14, 0),) and =INDEX($A$2:$A$14, MATCH(ROWS($A$1:A1), $C$2:$C$14, 0),)

 

[Lres81715]

Not sure this would work for me as the States and the State count ( columns A and B respectably) is actually a pivot table that's being pulled from website database sheet. It's extremely complicated but trust me, I can't alter it as it feeds a ton of other calculations to other sheets and macros.

The Top 10 is just a formula. Example E2=LARGE($B2:$B55,ROW($E$2:E2)

I just need D2:D11 to have a formula that checks the adjacent cell E2:E11 and give a UNIQUE state even if the number count is duplicated or triplicated.

 

[Aladin Akyurek]

@Lres81715

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td][/td][td]

10​

[/td][td][/td][/tr]


[tr][td]

2​

[/td][td][/td][td][/td][td][/td][td]

11​

[/td][td][/td][/tr]


[tr][td]

3​

[/td][td] States[/td][td] Count[/td][td][/td][td]Top Performer(s)[/td][td]Top Score(s)[/td][/tr]


[tr][td]

4​

[/td][td] California[/td][td] 17[/td][td][/td][td]California[/td][td]

17​

[/td][/tr]


[tr][td]

5​

[/td][td] Florida[/td][td] 10[/td][td][/td][td]Florida[/td][td]

10​

[/td][/tr]


[tr][td]

6​

[/td][td] Arizona[/td][td] 2[/td][td][/td][td]New York[/td][td]

6​

[/td][/tr]


[tr][td]

7​

[/td][td] Georgia[/td][td] 3[/td][td][/td][td]Texas[/td][td]

5​

[/td][/tr]


[tr][td]

8​

[/td][td] Illinois[/td][td] 2[/td][td][/td][td]Maryland[/td][td]

4​

[/td][/tr]


[tr][td]

9​

[/td][td] New York[/td][td] 6[/td][td][/td][td]Michigan[/td][td]

4​

[/td][/tr]


[tr][td]

10​

[/td][td] North Carolina[/td][td] 3[/td][td][/td][td]Alaska[/td][td]

4​

[/td][/tr]


[tr][td]

11​

[/td][td] Ohio[/td][td] 2[/td][td][/td][td]Georgia[/td][td]

3​

[/td][/tr]


[tr][td]

12​

[/td][td] Maryland[/td][td] 4[/td][td][/td][td]North Carolina[/td][td]

3​

[/td][/tr]


[tr][td]

13​

[/td][td] Michigan[/td][td] 4[/td][td][/td][td]Virginia[/td][td]

3​

[/td][/tr]


[tr][td]

14​

[/td][td] Alaska[/td][td] 4[/td][td][/td][td]Massachusetts[/td][td]

3​

[/td][/tr]


[tr][td]

15​

[/td][td] Virginia[/td][td] 3[/td][td][/td][td][/td][td]

​

[/td][/tr]


[tr][td]

16​

[/td][td] Massachusetts[/td][td] 3[/td][td][/td][td][/td][td]

​

[/td][/tr]


[tr][td]

17​

[/td][td] Texas[/td][td] 5[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

18​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Note. Alaska is added on purpose.

D1: 10 (Top N)

In D2 just enter:

=COUNTIFS(B4:B17,">="&LARGE(B4:B17,D1))

In D4 control+shift+enter, not just enter, and copy down:

=IF($E4="","",INDEX(A$4:A$17,SMALL(IF(B$4:B$17=E4,ROW(B$4:B$17)-ROW(B$4)+1),
    COUNTIFS(E$4:E4,E4))))

In E4 just enter and copy down:

=IF(ROWS(E$4:E4)<=$D$2,LARGE($B$4:$B$17,ROWS(E$4:E4)),"")

 

[Lres81715]

Thanks Aladin!

This is perfect. I was missing the COUNTIFS($E$2:E2,E2) at the end. I had Rows()

 

[Aladin Akyurek]

Thanks Aladin!

This is perfect. I was missing the COUNTIFS($E$2:E2,E2) at the end. I had Rows()

You are welcome.