Sort by largest, 2nd criteria by less than average %, 3rd criteria pre defined heading.

Hi all,

I have an issue I am hoping someone can help with.

In the Data tab of the attached workbook, I have 4 products, the products are listed by US State. They each have a number of people attached to them, and a popularity %
In the matrix tab the States are assigned to either East US, West US, or South US
The OAS tab is where I would like a formula to come in to play
In the example of product 1 in the OAS tab. The average popularity is 3.2%. I am looking for the largest 3 numbers in column D of the data tab of Product 1 that have a below average % in column E, (in this case 3.2%), and to sort by size ie. Large(D2:D15,1), and to retrieve the 3 largest number States, and to only retrieve in cells C6:C8 of the OAS tab States that are mapped to the East US

Then E6:E8 in the OAS tab would search Products 1 and search for the 3 largest numbers, for those below the average % in column E to retrieve the 3 states.

I have just written in the states to show what the data should be retrieving for product 1.

Is that possible to do with 1 formula? Hopefully I have explained it well enough.

Thanks,
Dean

I've updated the files with formulas. I am still unable to condense it down to one single formula (right now they are in column C & D), and am missing the additional variable of West US, East US, & South US.

I have no doubt someone here will be able to figure this one out ..

bebo, yes it is fine that they get rearranged that way in the matrix tab. This is a very interesting array formula! Thanks so much! will be marked as solved