Divide with Conditions in Excel

I need a formula that will divide a players contribution by the teams total contribution. So if this player plays for this team, divide his contributions by the teams contributions

Sheet 1

Player Name | Team Name | Player Contributions | % Share of Teams Contributions
Johnson     | Red Bulls | 14                   | Formula Needed

Sheet 2

Team Name | Team Contributions
Red Bulls       145 

I need a formula in Sheet 1 that basically says, if Team Name in Sheet 1 = Team Name in Sheet 2, Divide the Player Contributions by that specific Teams Contributions

I’m sure its a VLOOKUP or INDEX/MATCH formula but unsure on how to format it.

Thanks

>Solution :

VLOOKUP should work:

=C2/(VLOOKUP(B2,Sheet2!$A$2:$B$2,2,0))

Here,

• C2 represents Player Contribution
• B2 represents team name
• Sheet2!$A$2:$B$2 represents LOOKUP range
• 2 represents Team contribution

Sheet1:

Sheet2:

Note: Format the % Share of Teams Contributions accordingly