Retrieve latest value from mulitple columns

Clearly I have a lot to learn in excel. From rooting around the other threads in the forum and tips I think my question could involve lookup, match, if, index and more. Unforunately i've not come across the answer yet or more accurately i'm not clever enough to recognise the answer.

My problem I hope is best explained in the attached example worksheet. But if not....

I have a list of teams in 2 columns (columns A and B). These teams play matches against each other. The teams repeat themselves but vary in their order in which they play and whether they are home or away; i.e. appear in column A or B.

Each team has a points brought forward value in column C or D which correspond to the teams in column A and B respectively. They also then have a carried forward points value in column E or F produced after the points from the current game (not shown in the attached file).

I need to be able to find a formula which will retrieve the latest (last match) carried forward value for a team and enter it into the brought forward cell. This is made difficult because the team may last appear in columns A or B and there is no logic as to when they last played. It is easy enough to use lookup to find the value from the first row of an array but i cannot work out how to find the value in the last row.

Here's hoping someone does? Thanks.