How to choose maximum value

  • Thread starter Thread starter Omics
  • Start date Start date
O

Omics

Hi, I am wondering if anybody can help me with this. I need to take some
information from Table 1 to a new table which I am working on.
For example, for "apple" in column A, I would like to find the highest
number (30) in Column B and return the correspnonding
text in Column C (a2) to the new table.

Table 1
columnA ColumnB ColumnC
apple 2 a1
apple 30 a2
apple 15 a3
apple 6 a4
apple 12 a5
apple 9 a6
banana 3 b1
banana 10 b2
orange 2 o1
orange 1 o2
cherry 10 c1
peanut 20 p1
peanut 50 p2
peanut 25 p3
tomato 2 t1
tomato l t1
tomato 8 t3


Also, the new table has a lot of additionalinformation and therefore I can
not simple convert Table 1 to the new table.
The expected information needed to added to the new table is:

New Table
columnA ColumnB ColumnC
apple 30 a2
banana 10 b2
orange 2 o1
cherry 10 c1
peanut 50 p2
tomato 8 t3

Can I use the MAX function? Thanks lot !!

Omics
 
In sheet2 cell B1 apply the formula and copy down as required
=MAX(IF(Sheet1!A1:A100=A1,Sheet1!B1:B100))

In sheet2 cell C1 apply the formula and copy down as required
=INDEX(Sheet1!$C$1:$C$100,MATCH(1,(Sheet1!$A$1:$A$100=A1)*
(Sheet1!$B$1:$B$100=B1),0))

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"
 
Hi Biff,

Thanks so much. When I was trying to post my question today I tried to
logged in the excel discussion group via firefox. I waited for couple of
hours and did not see any response to my question. That's why I logged in
again via IE. After 30 minutes, I got two response. However, right now I can
not see my other post. Could you please direct me to find the other post or
paste please the answer here. Thanks.

Omics
 
Hi Jacob,

Thanks so much for your help! I followed your instruction and it works well.

Omics
 
Back
Top