Thread: Reference/Lookup formula

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.

Key question:

Originally Posted by fervorking

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.

implication of above is that have >1 row for any given Location & Category combination - your sample implies otherwise (1 row per combination)

This is a key point as it will have a direct impact on most appropriate solution
(single row would be pretty efficient - multiple rows less so)

Obviously "location" & "category" are dummy values in your file but can you confirm if the data is sorted in any way (eg by Location & Category: A-Z)

FWIW you should be avoiding Volatiles like INDIRECT if you have big-ish data sets - I've not really looked at the suggestions made but I suspect INDEX based alternatives would suffice.

Attached is one route which avoids Volatiles

Given version etc the use of concatenated column is important if you wish to remove need for Arrays/Sumproducts (inefficient)

The # returned in row below results are used to minimise repetitive cals in the INDEX & MATCH calcs.

In the attached 24 months of data is assumed - it's not clear if this is reality or not.

The formula returns the last text string within the precedent range (we assume strings < 255 chars in length)

This works because:

Originally Posted by D.O logic

a) LOOKUP assumes lookup_vector to be sorted in ascending order at all times (irrespective of reality) - and thus deems the last value to be the biggest

b) LOOKUP ignores all values within lookup_vector that are not of the same data type as the criteria

c) LOOKUP's job is to return the last value <= criteria as found in the lookup_vector (or return associated value from optional result_vector if specified)

Given all of the above:

=LOOKUP(REPT("Z",255),{"dog","carrot",1,2,#DIV/0!,"apple",2})

returns apple as it LOOKUP assumes it to be the biggest string given it appears last and it is still "less" than the criteria value

The same logic can be applied to numbers we simply modify criteria from string [REPT("Z",255)] to number [9.99E+307]

D.O. Logic - would be DonkeyOte logic I'm afraid...