# Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

1. ## Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

Hello Excelers!!

I don't believe the title best explains my problem, but I will attempt to explain it here.

In column A I have a list of the 50 US states, in B I have a list of numbers that corresponds to each state. These numbers will be updated daily some increasing a lot and some not at all. Anyways, I want to create a "top 5 list" that will update depending on which states have the highest counts. Linked to that list will be a simple pie chart that I also want to be "dynamic".

The main issue I run into is the repetition of states that have the same numbers. This all becomes more clear in the sample document. Look in column G for what I've attempted so far.

Any help and suggestions are very welcome!

Multiple Sorted Match Results.xlsx

2. ## Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

I think use of PivotTables would make your project look much more professional. It would be easy to maintain it and updating it would be just a click of a button.

3. ## Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

AlKey,

Sadly, I'm not well acquainted with pivot tables, where can I input my new data that comes in daily? I'm basically keeping a running tally for each state and the pivot table wont allow me to change any of its values. I'm hoping to keep this data behind the scenes and pull the charts i'm making into a "report" worksheet for viewing.

I added a sample of what kind of incoming data I'm getting (dumbed down a lot)

Multiple Sorted Match Results.xlsx

4. ## Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

Hi

E17
Formula:
`Please Login or Register  to view this content.`
copy down
D17
Formula:
`Please Login or Register  to view this content.`
This is array formula Ctrl+Shift+Enter Not Enter. copy down

Do the same top 5

H17 Same above E17 Large copy down

G17
Formula:
`Please Login or Register  to view this content.`
Ctrl +Shift+Enter then copy down.

Regard
micope21

5. ## Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

Micope21,

I appreciate you taking the time to put together those formulas for me, they all worked perfectly.

May I ask what the easiest way would be to sum any and all remaining states and throw those in the chart as an "others" category? Of course keeping the dynamic nature of the formulas?

6. ## Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

Hi

Not sure what you mean? If you looking for total? use sum like this =SUM(\$b\$2:\$b\$13)

Regard
micope21

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1