# Max amount within a range

#### Sun Ding

##### New Member
Good morning.
I need help with the following, thank you.
There are 2 columns: ID and rate.
Some IDs are repeated, sometimes 3 times, 5 times, 9 times etc.
I need to pick the highest rate for each ID and there are over 20,000 IDs. I tried DMAX but didn't seem to work.

FROM THIS TO THIS
ID Rate ID Rate
A01 22.53 A01 22.53
A02 22.64 A02 22.64
A03 22.98 A03 22.98
A04 22.98 A04 23.63
A04 22.98 A05 24.32
A04 23.40 A06 24.56
A04 23.63 A07 24.68
A05 24.32 A08 25.05
A06 24.56 A09 25.47
A07 24.68 A10 24.68
A08 25.05 A11 25.05
A09 25.47 A12 25.47
A09 24.56 A13 26.01
A10 24.68
A11 25.05
A12 25.47
A13 25.91
A13 26.01

Sorry, didn't realize that there were no spacing after I posted it.

Last edited:

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### BarryL

##### Well-known Member
i dont know what type of Microsoft Office you have but if its like mine (2010) you can use the below (newer versions have a MAXIFS i think?)

entered with ctrl+shift+enter
=MAX(IF(\$A\$1:\$A\$18=C1,\$B\$1:\$B\$18))

where column A is ID, B is rate and C are the ID's minus duplicates

#### Sun Ding

##### New Member
thank you for your prompt response.
I only got zeros.
I have ID:A1 in cell A2 and Rate:22.53 in cell B2 etc.
and I put the formula in cell C2.

I am using Office 2016.

Last edited:

#### Fluff

##### MrExcel MVP, Moderator
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">ID</td><td style=";">Rate</td><td style=";">ID</td><td style=";">Rate</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A01</td><td style="text-align: right;;">22.53</td><td style=";">A01</td><td style="text-align: right;;">22.53</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A02</td><td style="text-align: right;;">22.64</td><td style=";">A02</td><td style="text-align: right;;">22.64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A03</td><td style="text-align: right;;">22.98</td><td style=";">A03</td><td style="text-align: right;;">22.98</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">A04</td><td style="text-align: right;;">22.98</td><td style=";">A04</td><td style="text-align: right;;">23.63</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">A04</td><td style="text-align: right;;">22.98</td><td style=";">A05</td><td style="text-align: right;;">24.32</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">A04</td><td style="text-align: right;;">23.4</td><td style=";">A06</td><td style="text-align: right;;">24.56</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">A04</td><td style="text-align: right;;">23.63</td><td style=";">A07</td><td style="text-align: right;;">24.68</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">A05</td><td style="text-align: right;;">24.32</td><td style=";">A08</td><td style="text-align: right;;">25.05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">A06</td><td style="text-align: right;;">24.56</td><td style=";">A09</td><td style="text-align: right;;">25.47</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">A07</td><td style="text-align: right;;">24.68</td><td style=";">A10</td><td style="text-align: right;;">24.68</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">A08</td><td style="text-align: right;;">25.05</td><td style=";">A11</td><td style="text-align: right;;">25.05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">A09</td><td style="text-align: right;;">25.47</td><td style=";">A12</td><td style="text-align: right;;">25.47</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">A09</td><td style="text-align: right;;">24.56</td><td style=";">A13</td><td style="text-align: right;;">26.01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">A10</td><td style="text-align: right;;">24.68</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">A11</td><td style="text-align: right;;">25.05</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">A12</td><td style="text-align: right;;">25.47</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">A13</td><td style="text-align: right;;">25.91</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">A13</td><td style="text-align: right;;">26.01</td><td style=";"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">\$A\$2:\$A\$19,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">\$C\$1:C1,\$A\$2:\$A\$19</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">\$A\$2:\$A\$19=C2,\$B\$2:\$B\$19</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### BarryL

##### Well-known Member
put this in C2 and drag down (with cntrl+shift+enter)

=IFERROR(INDEX(\$A\$2:\$A\$19, MATCH(0,COUNTIF(C\$1:C1, \$A\$2:\$A\$19),0)),"")

then in D2 put (with cntrl+shift+enter)

=MAX(IF(\$A\$1:\$A\$18=C1,\$B\$1:\$B\$18))

#### Sun Ding

##### New Member
Amazing! Works like gem!
Thank you so much for your help! I spent an hour figuring how to do that, you guys are so brilliant!!

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback

1,095,223
Messages
5,443,183
Members
405,219
Latest member
CraneS

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...