Vlookup highest to lowest and return column name

Your data structure is pretty inefficient... You'd be better storing the data in a normalised form, then reporting it in a crosstab.

However, see the attached for a worked example which does what you want, using the rather nasty formula:

Formula:

Please Login or Register  to view this content.

I would suggest a helper column for the MATCH($C11,$F$2:$F$5,0) section might be in order.

Originally Posted by xlnitwit

I would suggest a helper column for the MATCH($C11,$F$2:$F$5,0) section might be in order.

I wouldn't - I'd suggest storing the original data in a better structure, in the first place!!

Indeed but, given the solution you provided, I think the helper column is in order.

I imagine we are all aware that realities of life often interfere with how things actually should be done.

Hi Olly,

Thank you for your post. From your solution you are matching the total turnover per customer (....;MATCH($C11;..) to get the required data as a result. However it is possible that from all the customers some would have the same (total) turnover. Won't this result in incorrect output? Can I use 'Customer' instead (....;MATCH($B11;....) since this is a unique value?

Thanks again.

Update:
I changed the MATCH lookup value to 'Customer' $B11 and altered the lookup array as well. Seems to work fine.