Lookup for the greatest value

[guti]

I need helpo for the following example:

I have the item number repeated in various rows and I need a formula that help me to select the greater value. For example a SUMIF will add them but I only need to select the greater value


<TABLE style="WIDTH: 379px; BORDER-COLLAPSE: collapse; HEIGHT: 155px" cellSpacing=0 cellPadding=0 width=379 border=0><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 48.75pt; mso-height-source: userset" height=65><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 48.75pt; BACKGROUND-COLOR: yellow" width=116 height=65>Item Number</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=65>Qty to order</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=67>how to select the greater value from B2:B3?? </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=70></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>XYC</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">30</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1"></TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1"></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #dbe5f1" height=20>XYC</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">45</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1"></TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1"></TD></TR></TBODY></TABLE>

 

[Matty]

Try:

=MAX(IF(A2:A3=A2,B2:B3))

Entered with CTRL+SHIFT+ENTER.

Matty

 

[guti]

Thanks Matty. what would you suggest if I have the data in the spreadsheet as follow:

XYZ 30
XYZ 40
XAB 10
XYZ 25
XAB 35
XYZ 25
XAB 25

 

[Aladin Akyurek]

Thanks Matty. what would you suggest if I have the data in the spreadsheet as follow:

XYZ 30
XYZ 40
XAB 10
XYZ 25
XAB 35
XYZ 25
XAB 25

Run Advanced Filter on the Item range (the range with XYZ, etc.) so that you have a distinct list of items in a different range than apply what Matty suggested:

Let A1:B8 house the example sample you provide (with A1:B1 housing headers like Item and Value.

Let E1:E3 house the result of Advanced Filter...

Item
XYZ
XAB

F2, control+shift+enter, not just enter, and copy down:

=MAX(IF($A$2:$A$8=E2,$B$2:$B$8))