Using HLOOKUP to determine the range to AVERAGEIFS

Hi guys,

Having a crack at forecasting sports results. I've attached a simplified version of the spreadsheet I am working with. Basically I would like to have a column of data showing the 'form' of the home team. I define form to be: the average margin from the past month's games.

Column A - date
Column B - home team
Column C - away
Column D - margin
Column E - team a's result (blank if a doesn't play)
Column F - team b's result
Column G - team c's result
Column H - team d's result
Column I - home team form

I would like column have the form of the home team. I was thinking of using AVERAGEIFS to take an average of the home teams results and use EDATE-1, to ensure if only averages the last month, but I have to specify a specific column to average. This is a problem because the home team changes every game and I would need to average a different column (i.e. column F if team b is the home team). I was thinking of using HLOOKUP for this, but I couldn't get it to work. Any ideas?
form.xlsx

Yes, in my full spreadsheet, there are games up until this year, not just 2013.

And in response to the earlier question, the values in column I should be the home team for that row's average result in the last month.
E.g. in row 16, d is the home team. In the month prior to this game, d only played once, 12 may, which is row 14. so the value in cell I16 would be -25

wenqq3, when I say last month, I mean in the last 30-31 days. I.e. if they played on 24th may, the last month would be 24 april-23 may.