formula help needed

[grahamcar]

Good Evening

I have set up a Football predictor to help raise money for my charity. I have just been entering the points as i go along but now my entries are starting to increase i have been looking into a way of excel doing it for me.

i cant seem to get it right though. I have a tab for every player, tab for the points (which is a two way table. Predition scores at top and actual scores on left with the points in the middle) tab for the games where i enter the actual score on so it goes to all players tabs straight away.

On the player tab i have the set up as follows

game/ home team/ actual score/ away team/ prediction / points

in the points column i need a formula to look up what the player predicted against the actual score to return the points from the table.

Can anyone help?

 

[FDibbins]

Is there really a need for a sheet/player? Excel often works best when all data is kept on 1 sheet, and all summaries etc pull from that 1 sheet

 

[grahamcar]

it makes it easier to track and keep other information like how much each player owes and if they pay a little more to save paying each week.

 

[grahamcar]

here is a sample from the points table

	0 0	0 1	0 2	0 3	0 4	0 5	0 6	0 7	1 0	2 0	3 0	4 0
0 0	3	1	1	1	1	1	1	1	1	1	1	1
0 1	1	3	1	1	1	1	1	1	1	1	1	1
0 2	1	1	3	1	1	1	1	1	1	1	1	1
0 3	1	1	1	3	1	1	1	1	1	1	1	1
0 4	1	1	1	1	3	1	1	1	1	1	1	1
0 5	1	1	1	1	1	3	1	1	1	1	1	1
0 6	1	1	1	1	1	1	3	1	1	1	1	1
0 7	1	1	1	1	1	1	1	3	1	1	1	1
1 0	1	1	1	1	1	1	1	1	3	1	1	1
2 0	1	1	1	1	1	1	1	1	1	3	1	1
3 0	1	1	1	1	1	1	1	1	1	1	3	1
4 0	1	1	1	1	1	1	1	1	1	1	1	3

<colgroup><col><col span="12"></colgroup><tbody>
</tbody>

here is a sample from the player tab

Game			actual			prediction	points
1	TOTTENHAM	1 0	0	1 0
2	ARSENAL	2 0	0	2 0

<colgroup><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>
</tbody>