Index and Match Function Questions 1

[Quintios]

(I thought I posted this, but it didn't seem to go through so if it's posted twice I apologize.)

I have a pivot table that has two data ranges in each row, and a total count in the data area. The table looks like this:

Plant Name |System Name |AlarmCount
PlantA          A            123
                B            445
                C            45
                D            10
PlantA Total                623
Plant B         A            976
                B            345
                C           1234
                D             45
PlantB Total                2600

and so on.

What I want to appear on the sheet in another area is something like this:

"The greatest number of alarms for this date range is:"
1234
from system C
at
Plant B"

That doesn't have to be in the same cell or anything, I can figure out how to format the thing. I can pull off the highest number of alarms in that column, and I can get the system name, what I can't figure out how to do is to get the Plant Name from the first column by referencing the MAX number from the third column (which is what I did to get the system name).

Any and all help is appreciated. This data comes from an external database and it takes a long time to run the query, so I'd prefer to keep the calculation within the database rather than add another pivot table to display the max value.

Much thanks in advance! Onwards,

Q-

 

[onedtent]

hopefully someone else can come up with a simpler formula

else, data being in cells A2, C11

=OFFSET(INDEX(B2:B11,(MATCH(MAX(C2:C5,C7:C10),C2:C11,0))),-VLOOKUP(INDEX(B2:B11,(MATCH(MAX(C2:C5,C7:C10),C2:C11,0))),A21:B24,2,FALSE),-1,1,1)

where A21:A24 are A,B,C,D
and
B21:B24 are 0,1,2,3

 

[Quintios]

I'm working on the forumla right now. I guess I did not indicate that the number of systems per plant varies as well. I haven't worked all the way through the forumla so I don't know if it will or will not work at the moment. I'll be back soon. Onwards,

Q-

 

[Quintios]

aHHH, I see, when I added the values that you described in the lower cells it works like a charm. I'm not sure what I'll do about the varying system name/number, but there is probably a way around it.

How in the heck did you come up with that? I think it'd take me a solid hour to understand that forumla...

Good stuff. Thanks for your help! Onwards,

Q-