How to get the maximum value by a certain criteria?

[Sandra Poh]

Dear Officer In Charge,
I am trying to do the below. Below is a list of price of my items and their
prices.
I would like to get the maximum price of a certain item. For example, for
the maximum of 305905 is 6.00. Is there any Excel worksheet function to find
out?
Something like "=MAX(A2:B13, B13)". Pls advise.

Item Price
305904 5.05
305905 5.05
305906 5.05
305907 5.05
305904 4.55
305905 4.55
305906 4.55
305907 4.55
305904 6
305905 6
305906 6
305907 6

Many thanks.
Sandra

 

[JE McGimpsey]

Dear Officer In Charge,
I am trying to do the below. Below is a list of price of my items and their
prices.
I would like to get the maximum price of a certain item. For example, for
the maximum of 305905 is 6.00. Is there any Excel worksheet function to find
out?
Something like "=MAX(A2:B13, B13)". Pls advise.

Item Price
305904 5.05
305905 5.05
305906 5.05
305907 5.05
305904 4.55
305905 4.55
305906 4.55
305907 4.55
305904 6
305905 6
305906 6
305907 6

First, this is a peer-to-peer group. There's nobody in charge (much less
an officer), just users like yourself.

One way:

=MAX(IF(A2:A13 = A13, B2:B13))

This is an array formula, and should be entered into the cell by
pressing CMD-RETURN, not just Return. If you do it right, you should see
brackets around the formula in the formula bar, like this:

{=MAX(IF(A2:A13 = A13, B2:B13))}

(don't try to put the brackets in by hand - it won't work).

 

[Carl Witthoft]

First, this is a peer-to-peer group. There's nobody in charge (much less
an officer), just users like yourself.

Speak for yourself. Signed, The Galactic Overlord of Excel


Anyway, JEM's solution below looks fine. If you dislike array
formulas, you could also get there with a couple columns of intermediate
results, such as =IF(A2=305904,B2,0) (fill down the column) in column
C,
then a cell in column D takes =MAX(C2:C13)
But that's ugly

 

[Sandra Poh]

Dear Carl and JE,
Many thanks for your guidance. I have tried your methods and it works.
Thousands of gratitude here.

Best Regards
Sandra Poh

 

[Sandra Poh]

Dear Carl and JE,
I have done using MAX(INDEX(($X$3:$X$4028=$X3)*L$3:L$4028,0)) to find the
maximum value, now I want to find the minimum value. I tried
MIN(INDEX(($X$3:$X$4028=$X3)*L$3:L$4028,0)) or
MIN(INDEX(($X$3:$X$4028=$X3)*L$3:L$4028,1)), the return value is 0 and I
tried MIN(INDEX(($X$3:$X$4028=$X3)*L$3:L$4028,2)). It works for the 1st 2
rows but I drag the formula to other rows and it does not work.

Pls help.

Regards
Sandra