Match Index with Multiple results found and retrieve unique value

Lres81715

Board Regular
Joined
Aug 26, 2015
Messages
147
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)))
 
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),)
 
Upvote 0
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.
 
Upvote 0
@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:
Rich (BB code):
=COUNTIFS(B4:B17,">="&LARGE(B4:B17,D1))

In D4 control+shift+enter, not just enter, and copy down:
Rich (BB code):
=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:
Rich (BB code):
=IF(ROWS(E$4:E4)<=$D$2,LARGE($B$4:$B$17,ROWS(E$4:E4)),"")
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top