Match text next to a variable over multiple worksheets

MarkMagic

New Member
Joined
Aug 29, 2013
Messages
3
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,215,529
Messages
6,125,344
Members
449,219
Latest member
Smiqer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top