IF Function with ranking

[ygao85]

Hi guys,

I don't know if you guys can help me find a formula for the following:

Player City Points
A New York 12
B L.A. 30
C Chicago 22
D Chicago 15
E Boston 27
F New York 18
G Boston 11
H Chicago 25
I Chicago 26

So I have a list of players, their cities and the points they have. I would like a formula that would show me the highest ranking player by city. What I am trying to accomplish is entering a city (i.e., New York) and the result would be Player F (highest ranking player from NY).

Thanks in advance!

[Special-K]

=INDEX(A1:C9,MATCH(MAX(IF(E1=B1:B9,C1:C9)),C1:C9,0),1)
Array formula, use Ctrl-Shift-Enter

NOTE: This does not cater for players where points are tied, ie it will only return the FIRST highest ranking not all

[José Augusto]

Or a non array formula
=INDEX($A$1:$A$9,MOD(AGGREGATE(14,6,($C$1:$C$9+ROW($C$1:$C$9)/100)*--($B$1:$B$9=E1),1),1)*100,1)

Note: E1:E4 is a list of cities.

[ygao85]

What would be the modification to this formula if I wanted to created a table that ranked the players of a certain city by their points? (ie, 1st place, 2nd place, 3rd place, etc...)

Thanks!

[JeteMc]

What would be the modification to this formula if I wanted to created a table that ranked the players of a certain city by their points? (ie, 1st place, 2nd place, 3rd place, etc...)

How about a pivot table that filters cities using either a slicer or report filter. The player names would be assigned as row labels and the points would be values with a sort largest to smallest applied.
Here is a copy of your file with a pivot table applied: Ranking by two criteria.xlsx
Let me know if you have any questions.

[Tony Valko]

=INDEX(A1:C9,MATCH(MAX(IF(E1=B1:B9,C1:C9)),C1:C9,0),1)

That formula could return an incorrect result if the max number also corresponds to a different team.

Data Range

	A	B	C	D	E	F
1	A	New York	12		New York	C
2	B	L.A.	30
3	C	Chicago	18
4	D	Chicago	15
5	E	Boston	27
6	F	New York	18
7	G	Boston	11
8	H	Chicago	25
9	I	Chicago	26
10	------	------	------	------	------	------

Try it like this...

=INDEX(A1:A9,MATCH(MAX(IF(B1:B9=E1,C1:C9)),IF(B1:B9=E1,C1:C9),0))

Still array entered.