Finding max value from one column, from cells determined by value in another column

[andreas_udby]

I could have sworn there was a way to do this built into Excel, but I'm not finding anything that works so far. I'm going to resort to example names to make the state of things clearer.

In essence, I have two columns: Child and Grade.

Child has five potential values: Annie, Bobby, Clare, David, and Elisa.

Grade has many different potential numerical values, from 0.0 to 4.0.

I had thought there was a straightforward way to find the maximum value in one column based on determining values in a corresponding column, like this:

[U]Child          Grade[/U]
Clare          3.4
Bobby          2.9
Clare          2.7
David          3.6
Elisa          3.5
Annie          3.0
Clare          3.5
Elisa          2.8
David          3.1
David          3.0
Bobby          3.2

So, if I wanted to find the highest grade for Clare, I could tell Excel, "Find the maximum value in column Grade, but limit the list of values to choose from to the values that correspond to 'Clare' in column Child".

I've tried no end of combining INDEX(), MATCH(), MAX(), DMAX(), various lookups, and so forth, to no avail. I'm not even sure how to set up an IF() statement to capture what I'm looking for. Manually sorting the columns isn't an option, as I need Excel to be returning these values on the fly as various other inputs are made.

Was I just dreaming that there was a way to do this? I wish there was some kind of MAXIF() function similar to the SUMIF() and COUNTIF() functions.

Thanks,
Andreas

 

[qafself]

can you rearrange the data so that names go down and grades go across? That would make it simple

 

[chergh]

A pivot table would be my weapon of choice here.

 

[andreas_udby]

Actually, I found a way to do it on another site. I realized that what I was looking for was a MAXIF() type of thing, so I started Googling around for that. Here's the successful candidate:

{=MAX(IF(A2:A12="Clare",B2:B12))}

I honestly don't understand how it works, but somehow it does. You have to remember to enter it as an array formula, though (CTRL+SHIFT+ENTER), or else it won't evaluate properly.

Thanks for everyone's suggestions!