Copying the value in a cell based on other cells matching

Question

I'm sure this is a simple problem to solve but I am struggling so thought I would seek help.

Basically I have the following setup:

Team Names in Column A
Points scored in Column B

I want to then enter the Team Names in Column C and to then have a formula in Column D which checks the name I've added to Column C against the names in Column A and automatically shows the points scored from Column B.

So as an example -
Column A = "Man Utd"
Column B = "3"
I then type "Man Utd" into Column C and would want "3" (from Column B) to automatically appear in Column D.

I guess this is possible but I have tried various VLOOKUP, MATCH, IF (to name a few) formulae with no joy.
If anyone can assist it would be appreciated.

Edited Addition:

The spreadsheet I am devising is to help me with the scoring of a football (or soccer depending where you're from) forum game I run. The idea is that each player picks 10 football teams and they get a point for each goal scored. However, they also pick one team which they think will not score any goals. If their team doesn't score they get 3 points but if the team does score they lose a point for every goal the team scores.

For example - Example A) The player picks Man Utd to not score a goal but they score 3. This means that the player would score -3 points. Example B) The player picks Man Utd to not score a goal and they don't. This means that the player would score 3 points.

Is there a way to create a similar formula to what has been suggested below that, for this selection, would match the team in the list in Column A to what I have entered in Column C and if the score next to this team (in Column B) is "0" it allocates 3 points to the relevant cell in Column D but if the score in Column B is above 0 (eg 3), the result in the relevant cell of Column D reads as minus the value shown in Column B (eg -3).

Hope this makes sense!

Thanks again

Answer 1

Please try

=IFERROR(VLOOKUP( C2,A2:B,2,0),"pick a team")

You need to use 0 or FALSE if the teams are NOT sorted. You otherwise use 1 or TRUE.

If you want to return more than one teams you will also need the ARRAYFORMULA function.

=ArrayFormula(IFERROR(VLOOKUP(C2:C,A2:B,2,0)))

Functions used:

• ArrayFormula
• VLOOKUP
• IFERROR