Find Max value based on criteria in Excel 2010

[gmich13]

I am trying to find the max value from column B based on a value in column A. Both columns values are numbers.

A B
100 5
100 5.5
100 6
100 7
110 5
110 6
110 15

I have tried using IF statements but I must be missing something.

 

[steve the fish]

Provided the max value is a positive number you can use:

=MAX(INDEX(($A$1:$A$7=A1)*$B$1:$B$7,0))

If not you can use:

=MAX(IF($A$1:$A$7=A1,$B$1:$B$7))

but this one needs CTRL-SHIFT-ENTER not just ENTER to enter it into a cell.