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.

WEEK	HRES	HSCORE	HOME	AWAY	ASCORE	ARES
5	LOSE		NORWICH	ASTON VILLA	1	WIN
5	WIN	4	MANCHESTER CITY	MANCHESTER UNITED	1	LOSE

<tbody>
</tbody>

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.

Team	3	4	5
Norwich	LOSE	DRAW
Aston Villa	LOSE	WIN
Manchester City	WIN	DRAW
Manchester United	WIN	WIN

<tbody>
</tbody>

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!