Find MAX number with criteria

  • Thread starter Thread starter GoBow777
  • Start date Start date
G

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.
 
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.
 
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.
 
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
 
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
 
GoBow777;834553 said:
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.
Click to expand...

Thanks to all who replied, everything works great now. Thank you.
 
GoBow777;834553 said:
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.
Click to expand...

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