IF Formula help needed - football results spreadsheet

[iamjj23]

Hi there, I have just found this site and hopefully someone can help with them problem im faced with. I have one sheet within the workbook with the Table shown below where I track the football results from all English and Scottish leagues. [TABLE="width: 500"]
<tbody>[TR]
[TD]WEEK
[/TD]
[TD]HRES
[/TD]
[TD]HSCORE
[/TD]
[TD]HOME
[/TD]
[TD]AWAY
[/TD]
[TD]ASCORE
[/TD]
[TD]ARES
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]LOSE
[/TD]
[TD][/TD]
[TD]NORWICH
[/TD]
[TD]ASTON VILLA
[/TD]
[TD]1
[/TD]
[TD]WIN
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]WIN
[/TD]
[TD]4
[/TD]
[TD]MANCHESTER CITY
[/TD]
[TD]MANCHESTER UNITED
[/TD]
[TD]1
[/TD]
[TD]LOSE
[/TD]
[/TR]
</tbody>[/TABLE]
By typing the score into HSCORE and ASCORE I am returned the match result for each team in HRES and ARES. In another sheet I have the below table where the last weeks match result is input to form a week by week form guide.[TABLE="width: 500"]
<tbody>[TR]
[TD]Team
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Norwich
[/TD]
[TD]LOSE
[/TD]
[TD]DRAW
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aston Villa
[/TD]
[TD]LOSE
[/TD]
[TD]WIN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Manchester City
[/TD]
[TD]WIN
[/TD]
[TD]DRAW
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Manchester United
[/TD]
[TD]WIN
[/TD]
[TD]WIN
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Currently I am having to input the match result for each week in manually but what I would ultimately like is to input a formula into each week that would copy the match result from table 1 to table 2 as the scores are input. I have tried a couple of IF statements but struggling to get my head around it. Any help much appreciated.

 

[Chris Mack]

Assuming the first set of data is on "Sheet1", here is a working formula for cell D2 on the other sheet:

=IFERROR(INDEX(Sheet1!$B$2:$B$3,MATCH($A2&D$1,Sheet1!$D$2:$D$3&Sheet1!$A$2:$A$3,0)),INDEX(Sheet1!$G$2:$G$3,MATCH($A2&D$1,Sheet1!$E$2:$E$3&Sheet1!$A$2:$A$3,0)))

Entered with CTRL+SHIFT+ENTER.

This will work for the entire table (just "copy" and "paste formulas"). Obviously, you can also increase your "Sheet1" ranges to whatever is appropriate.

Hope this helps,

Chris.

 

[iamjj23]

Thanks Chris this works a treat! Very much appreciated!

 

[Chris Mack]

You're welcome!