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
 
Ad

Advertisements

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)
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

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
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top