Reference/Lookup formula

[fervorking]

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.

[DonkeyOte]

Key question:

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)

[brokenbiscuits]

I think does what you need?

[fervorking]

@DonkeyOte:

Sorry for the confusion. There is only one row per combination of Location and Category. The data is not sorted in any way. The locations are grouped by region (south, east, etc.), and the categories are grouped by type (rev, exp, margin, etc.).

@brokenbiscuits

Your solution does accomplish what I need it to. I had to tweak the formula on "Sheet1" a little because the date format wasn't the same as mine (I'm in the States). However, I was trying to avoid going the route of text strings and concatenations. I'm trying to setup the file to be a "drop data and calculate" type so another user can drop in the current months data and press F9 without having to do anything else. Thanks for your help though.

[brokenbiscuits]

Yeah, you crazy Americans and your crazy date formats!

If the user is just dropping in one month's worth of data though he shouldn't need to amend any concatenates. The concatenates would only need to be amended if the number of lines changed. To future-proof that, you could even pull the concat formula down by x lines, depending on the potential number of locations and/or categories.

Also, if you change cell B1 on the Summary sheet to =MAX(Data!1:1), everything should calculate automatically once you drop in a new month's worth of data, nothing else is required.

[brokenbiscuits]

(then obviously hiding the concatenates column, otherwise it would look a bit of a mess!)

[fervorking]

I ended up just going with a slightly modified version of what you did. That was my initial idea, but I thought I'd try and learn a new way to do it. Thanks for your help though. I don't know if I would have thought of using INDIRECT. Working great so far.

[DonkeyOte]

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.

[fervorking]

I've heard that about INDIRECT before, and I would really like to use an INDEX-based alternative. I just don't know the reference formulas well enough to pull it off.

[DonkeyOte]

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.

[fervorking]

This is more like what I was looking for. I understand the logic for all of it except the LOOKUP(REPT("Z",255)... portion. Can you explain the logic behind this?

[DonkeyOte]

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

This works because:

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:

Please Login or Register  to view this content.

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]

[fervorking]

Got it. Thanks for the explanation. Is that quote from D.O Logic part of a longer list? Or do you know a website with those sorts of explanations for other formulas. That would be a huge help.

[DonkeyOte]

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

[fervorking]

Haha got it. Thanks for all the help.