Returning highest ranking values in a range in succession

Pic attachment of exampleExcel.jpgExcel.jpg




I am trying to find two formulas:

Formula 1
Formula 1 will be entered into rows 6:9 (in this example). I need it to return the highest selling product (by store) in order form highest selling to lowest selling. I have a makeshift formula already but it cannot report in order (only the highest selling) and it cannot get past the one return index( limitation. When it finds two high sellers it prioritizes the ranking based on how well that product has sold across the other stores. Product 1 sold 13 times across all stores. Product 2 sold 9 times across all stores. So if product 1 & 2 tie for Store 3 (as shown in the example) Product 1 would be chosen based on its sales across the row.

Formula 2
Same as formula 1 but with Stores (in column 6-9). It reports the highest selling store and treats ties the same way as formula 1.

close, but I couldn't adapt it. also, the ranking isn't prioritizing at the right place. I included more detailed information in the excel file you sent me. Formula sort.update.xlsx

also, I'd like to try to avoid an {} formula if possible

sorry, I also forgot to mention that it would need to avoid detecting 0s and "". Any blank spaces in the datarange will return 0.

an array is better than nothing, just didn't know if it was possible without one.

yes, the data is fictitious. You can enter whatever you want in it... Thank you for your help. I just want to make sure it can sort through tied numbers. If you want the real excel workbook I can send that if necessary.

I posted last night to let you know it worked but for some reason the post didn't stick. It took a while to adapt the formula but it works like a charm. Thank you very much.