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.