Hello, I have been searching for a solution to this with no luck so will explain my problems here:
I have three worksheets relating to three football teams. On each of these worksheets, B2:B19 contains the names of the players and C2:C19 contains the number of goals that each player has scored. Cell C23 contains the name of the highest goal scorer in that particular team - =MAX(C2:C19). C24 contains the number of goals the highest scorer has scored - =INDEX(B2:B19,MATCH(MAX(C2:C19),C2:C19,0)).
Worksheet four is a statistics page. One statistic it shows is the highest overall goal scorer of all three teams - =MAX(Team1:Team3!C24).
My first problem is if there are two or more players that have scored an equal amount of goals as only one name is displayed using this formula. How can I adjust my formula to allow for this?
My other problem is on worksheet four. I would like the adjacent cell to the highest number of goals to show the name of the player that has achieved this. Again, equal high scorers will be a problem, but for a single high scorer, am I way off with this?:
=INDEX(Team1:Team3!C23,MATCH(MAX(Team1:Team3!C24),Team1:Team3!C2:C19,0)).
I'm no Excel expert, I just coined this together from bits I've read on other websites and now I'm all out of ideas! Any help appreciated.
Thanks,
Mark.
I have three worksheets relating to three football teams. On each of these worksheets, B2:B19 contains the names of the players and C2:C19 contains the number of goals that each player has scored. Cell C23 contains the name of the highest goal scorer in that particular team - =MAX(C2:C19). C24 contains the number of goals the highest scorer has scored - =INDEX(B2:B19,MATCH(MAX(C2:C19),C2:C19,0)).
Worksheet four is a statistics page. One statistic it shows is the highest overall goal scorer of all three teams - =MAX(Team1:Team3!C24).
My first problem is if there are two or more players that have scored an equal amount of goals as only one name is displayed using this formula. How can I adjust my formula to allow for this?
My other problem is on worksheet four. I would like the adjacent cell to the highest number of goals to show the name of the player that has achieved this. Again, equal high scorers will be a problem, but for a single high scorer, am I way off with this?:
=INDEX(Team1:Team3!C23,MATCH(MAX(Team1:Team3!C24),Team1:Team3!C2:C19,0)).
I'm no Excel expert, I just coined this together from bits I've read on other websites and now I'm all out of ideas! Any help appreciated.
Thanks,
Mark.