Find MAX number with criteria

[GoBow777]

Hello all:

Using Excel 2003

I have two lists, column A contains a list of names that have
reoccurrences of the same name many times within the list and I’ve
already made a list in column E of those unique names. Column B is a
list of numbers in random order and no two numbers are the same.

My problem is trying to make a new list in column F by finding the MAX
number in column B that is associated with the persons name in column
E.

I’ve tried many different formulas with no success, can any one please
help me out with this.

 

[T. Valko]

Try this array formula** :

=MAX(IF(A$1:A$100=E1,B$1:B$100))

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

Copy down as needed.

 

[Bernard Liengme]

Let's say the names are in A2:A140, the numbers in B2:B140
Let's say the first name in E is in E2

In F2 enter =MAX(IF($A$2:$A$140=E2,$B$2:$B$140))
But since this is an array formula commit it with CTRL+SHIFT+ENTER not just
ENTER
The formula bar will display {=MAX(IF($A$2:$A$140=E2,$B$2:$B$140))} but
Excel not you has added the braces

Copy F2 down the column

Do NOT try to use full columns references =MAX(IF(A:A=E2,B:B)) unless you
are working in Excel 2007
best wishes

 

[Bernd P]

Hello,

I suggest to use my UDF Mfreq:
http://www.sulprobil.com/html/mfreq.html

Please note that this is an array function. Select your desired output
area and then enter the function with all necessary parameters and
finish with CTRL + SHIFT + ENTER, not only ENTER.

Regards,
Bernd

 

[GoBow777]

Hello all:

Using Excel 2003

I have two lists, column A contains a list of names that have
reoccurrences of the same name many times within the list and I’ve
already made a list in column E of those unique names. Column B is a
list of numbers in random order and no two numbers are the same.

My problem is trying to make a new list in column F by finding the MAX
number in column B that is associated with the persons name in column
E.

I’ve tried many different formulas with no success, can any one please
help me out with this.

Thanks to all who replied, everything works great now. Thank you.