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