Hi folks,
I have a list of 5 products and each of the 5 products are produced in 2 different cities, with sales in each city (the cities aren't really important for the solution, just FYI).
5 of the cities (or 50% of the cities) are in a column called Region 1 and the remaining 50% are in Region 2.
I want to be able to return which product has the highest sales, depending on the Region selected from a drop down list.
Drop down list choice: Region 1 or Region 2
<tbody>
</tbody>
I want the formula to return "Ball Bearings" if I chose Region 1 (from my drop down list) and "Engines" if I chose Region 2.
I already have a formula that works (but it's inefficient) that uses INDEX/IF/MATCH/MAX/MATCH/MAX, but I want to avoid using IF statements and hopefully rely on a cleaner formula that just uses INDEX/MATCH/MAX and or something else. Arrays are ok too.
Many thanks for your time to read and help out.
James
I have a list of 5 products and each of the 5 products are produced in 2 different cities, with sales in each city (the cities aren't really important for the solution, just FYI).
5 of the cities (or 50% of the cities) are in a column called Region 1 and the remaining 50% are in Region 2.
I want to be able to return which product has the highest sales, depending on the Region selected from a drop down list.
Drop down list choice: Region 1 or Region 2
Product | Region 1 | Sales ($millions) | Region 2 | Sales ($millions) |
Engines | City A | 10 | City F | 42 |
Turbo fans | City B | 5 | City G | 5 |
Lubricants | City C | 8 | City H | 5 |
Ball bearings | City D | 21 | City I | 33 |
Hydraulics | City E | 15 | City J | 11 |
<tbody>
</tbody>
I want the formula to return "Ball Bearings" if I chose Region 1 (from my drop down list) and "Engines" if I chose Region 2.
I already have a formula that works (but it's inefficient) that uses INDEX/IF/MATCH/MAX/MATCH/MAX, but I want to avoid using IF statements and hopefully rely on a cleaner formula that just uses INDEX/MATCH/MAX and or something else. Arrays are ok too.
Many thanks for your time to read and help out.
James