Need Formula To Return Value From Data Table Based On 3 Selectable Criteria

Good afternoon,

I'm wondering if someone can take a look at the attached and help a dude out. In the attached, I am hoping to return the location numbers from the Data Tab Column A where, in this example, the top 10 ranks occur in the Data Tab Column B. I suppose the kicker is finding a way to have the formula able to function as the 3 criteria changes in the Dash tab (i.e. shifts from Top 10 to Bottom 10 for the different demographic headers). I've been toying with am If Match Index Large and Small function for a bit too long and I'm not finding what might be a simple solution. So, thanks in advance for being a second set of eyes.

How about using the Autofilter in Excel. You can filter on Top 10 or Bottom 10.

http://www.k2e.com/tech-update/tips/...-top-ten-items

If I were to look to do it alternatively to manually auto filter, I would write some VBA to
1. to clear the Sheet2 of data
2. Autofilter based upon the criteria in A1:A3 on sheet 2
3. Copy the data autofiltered to Sheet 2.

I don't know if what you are looking to do can be done with formula. However, someone else may step in here and offer up a formula solution.

Alan