max

[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. 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" ?

 

[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)
If your text ex: "apple" is already on your table, just use cell reference
instead of the "words",
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)
Adjust range to suit.
HTH
John

 

[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.

 

[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

 

[John]

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

 

[John]

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

 

[T. Valko]

I'm getting better everyday.

Me too. At least, I hope so!