I'm not exactly sure how to describe what I need, but I believe it is some combination of INDEX, MATCH, VLOOKUP, HLOOKUP, IF, and possible other formulas. I've found pieces online, but I just cannot figure this one out after a couple hours of trying.
Here's what I'm trying to accomplish (refer to attached workbook):
Worksheet "Summary" contains a row for each location (Location#), a section for each category (Cat#), and four columns labeled "Best Mth", "Total", "Best Dec", and "Total." Each will have a different formula. Note: all data is on the "Data" tab.
First "Total": Finds the MAX for the location and the category (e.g. Location1, Cat1) and returns the value
"Best Mth": Returns the corresponding month for the first "Total" above
Second "Total": Finds the MAX for the location, category, and current month found in cell B1 (e.g. Location1, Cat1, Dec). For the month in B1, it would be the month of December for Dec-10, September for Sep-10, etc.
"Best Dec": Returns the corresponding month for the second "Total"
I could do this manually, but the sample workbook is much, much smaller than what I'm actually dealing with (20+ locations, 12 categories, 10 years).
Any help is greatly appreciated. This is driving me crazy.
Bookmarks