# MAX value in one coulmn based upon another column

Solved/Closed
Piggy70 Posts 3 Registration date Wednesday May 29, 2013 Status Member Last seen May 30, 2013 - May 29, 2013 at 05:22 PM
Piggy70 Posts 3 Registration date Wednesday May 29, 2013 Status Member Last seen May 30, 2013 - May 30, 2013 at 08:55 AM
Batch Price Column C
00015 2.75
00015 2.9995
00016 1.89
00017 0.56116
00018 0.1421
00018 0.11808
00018 0.1605
00018 0.12575
00018 0.1091
00018 0.1745
00018 0.1342
00018 0.1655
00019 0.1421
00019 0.11808

Hi, I have about 55,000 rows like the above and I am trying to get the maximum price within each batch to display in Column C....example, for Batch 00015, the max price should be \$2.9995 (displayed in Column C) - I do not know how to do this in EXCEL - can anyone assist? thank you
Related:

## 2 responses

Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
May 30, 2013 at 04:30 AM
Hi

Try the following array (CTRL+ SHIFT + ENTER to commit). Assuming the batch numbers are in A1:A14 & prices are in C1:C14, also assuming that the batch numbers are "text".

=MAX(IF(A1:A14="00015",C1:C14))

If they are numbers in column A, formatted as "00000".

=MAX(IF(A1:A14=15,C1:C14))

You might want to look at the aggregate function if you have Excel 2010>.
Piggy70 Posts 3 Registration date Wednesday May 29, 2013 Status Member Last seen May 30, 2013
May 30, 2013 at 08:55 AM
Thank you so much! It works beautifully!