Lookup the highest value per criteria

[xmoore]

Guys,

Would need your expertise on this.

I'm trying to capture the highest number on a given group. Been playing around with index and match and I can't seem to make it worked.

For example,

Group Name, count
group A, 12
group A, 13
group A, 15
group B, 3
group B, 2

the output cell should give me something like:
Group Name, max count
group A, 15
group B, 3

thanks guys...

[DonkeyOte]

You should use a Pivot Table - much more efficient - set Group Name as Row Field/Label and Count set as Data Field set to MAX.

[sweep]

If you prefer a formula

=SUMPRODUCT(MAX((A1:A5="group A")*B1:B5))

will return the maximum value of "group A"

[xmoore]

Thanks DonkeyOte... as much as I want to use the pivot table I can't. I need the formula base.

Appreciate the help Sweep. It did work just the way I wanted.

thanks a lot guys.

regards,
xmoore