Finding max value in column A where value in B =

[meolesen]

Anyone knows how to find the max value in one column where the value
in another column match a particular search criteria. The sheet looks
something like this:

A B
Peter 5
James 3
Peter 7
James 6
Peter 2

I need to find the row with the highest number for Peter. Finding the
row (row 3) would be the best, but finding the number (7) could also
do because the numbers are almost certainly unique.

Thanks in advance.

 

[Guest]

With
A1:B5 containing your posted list....
Peter 5
James 3
Peter 7
James 6
Peter 2

AND...
C1: (a name from the list....eg. Peter)

Try this regular formula:
D1: =MAX(INDEX((A1:A5=C1)*(B1:B5),0))
Note: that formula only works if the max value for the name is NOT negative.

or this ARRAY FORMULA (commited with ctrl+shift+enter)
D1: =MAX(IF(A1:A5=C1,B1:B5))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Try this:

=MATCH(MAX(IF($A$1:$A$5="Peter",$B$1:$B$5,0)),$B$1:$B$5,0)

This is an array formula. It should be entered with CTRL-SHIFT-ENTER
instead of just Enter. If done properly, the formula should be enclosed in {
}

HTH,
Elkar

 

[meolesen]

Super Thanks Ron - the first formula does exactly what I was looking
for ;-)

Regards,
Martin

 

[Guest]

A slight variation of Rons and Elkars suggestions to get the row number

=MATCH(MAX(IF(A1:A5=C1,B1:B5)),(A1:A5=C1)*(B1:B5),0)

array entered. Just in case some of the numbers are duplicated among
different people.