# How to choose maximum value

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

T

J

#### Jacob Skaria

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>}"

O

#### Omics

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

Omics

O

#### Omics

Hi Jacob,

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

Omics