# max

O

#### Omics

Dear All,

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

#### T. Valko

New Table
columnA ColumnB ColumnC
apple 30 a2
banana 10 b2

Do you already have "apple 30" in the new table or do just have "apple" ?

J

#### John

Hi Omics
This goes in column B =MAX(IF(\$A\$1:\$A\$20="apple",\$B\$1:\$B\$20))
and this one in column C
=VLOOKUP(MAX(IF(\$A\$1:\$A\$20="apple",\$B\$1:\$B\$20)),\$B\$1:\$C\$20,2,0)
like this =MAX(IF(\$A\$1:\$A\$20=A1,\$B\$1:\$B\$20))
=VLOOKUP(MAX(IF(\$A\$1:\$A\$20=A1,\$B\$1:\$B\$20)),\$B\$1:\$C\$20,2,0)

They are both array formulas, you must enter them by pressing (Control Shift
Enter)
HTH
John

T

#### T. Valko

=VLOOKUP(MAX(IF(\$A\$1:\$A\$20=A1,\$B\$1:\$B\$20)),\$B\$1:\$C\$20,2,0)

That will return an incorrect result if another item has the same max value
and is further down the list.

You have to lookup both apples and the max for apples.

Table 2
E2 = apples

For the max for apples. Array entered** in F2:

=MAX(IF(A\$1:A\$20=E2,\$B\$1:\$B\$20))

For the lookup. Array entered** in G2:

=INDEX(C\$1:C\$20,MATCH(1,IF(A\$1:A\$20=E2,IF(B\$1:B\$20=F2,1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

O

#### Omics

Hi John,

Biff is right. I tried the VLOOKUP function and it returned incorrect
results when another item has the same max value. Thanks a lot for your help.

Omics

J

#### John

Thank you Biff, for your correction. I'm getting better everyday.
Regards
John

J

#### John

You're welcome, but no cigars for me. I didn't test it enough.
Regards
John

T

#### T. Valko

I'm getting better everyday.

Me too. At least, I hope so!