Combine Row name with MAX and Column Heading

[jrobin5881]

Good Morning,

I have a small spreadsheet 8 rows by 11 columns (Excel ver 2003)and two rows below each column I added the formula = MAX(C2:C10) to give me the highest value. Then I decided I would like the name of the unit which is in Column A to preceed the MAX value and the column heading to follow after the value so that it would look something like this:

West 2,499,00 Widgets.

2,499,000 is MAX units produced in Column C - West is unit name in Column A directly to left of MAX units and Widgets is the Column heading name in Column C

I tried combing the ampersand and using Offset to grab the row and col values but its not working.Here's my failed formula. =MAX(C2:C10)& Offset(MAX(C2:C10),0,-2) & C1. I'm figuring my MAX statement will find the highest value then I offset 0 rows and two columns left to get the unit name and I can hard code the column heading which in this case is C1.

Any ideas out there?

 

[SkipVought]

[tt]
=A1&" "&Max(C2:C10)&" "&C1
[/tt]


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[jrobin5881]

Thanks Skip but the problem with the solution you gave is that the Unit name in Column is different for each value which is the sticking point. It is not an absolute So when we find the max val we must look to the left in the same row to get the unit name for the value. The Column heading is a constant.

 

[SkipVought]

Pleas post a sample of your data that illustrates this point.

Have you checked out the PivotTable wizard?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[Fenrirshowl]

Think you might need:
=index(A2:A10,match(Max(C2:C10),C2:C10,0)) & " " & Max(C2:C10) & " " & C1

Will you ever get a situation where you have 2 rows with the same maximum value? If so, this will only return the info connected to the first value.

 

[jrobin5881]

Fenrirlshowl,

This is exactly what I was looking for. Thanks! I realize that I could use a pivot table but your solution works best for such a small amount of data. In addition the odds are extremely low that I would get two MAX values and if so I'd overwrite the formula. Many thanks again.