Excel 2007 : INDEX/MATCH help required

**5foot9** · 07-22-2011, 10:25 AM

Hi Everyone,

This is my first time here.

I have a list of football results and I'm trying to make a record of recent results (last 6 results for now) for each team next to each match (1=home win, 2= away win, 3= draw) and an overall record of home and away results for each team next to that.

I have columns next to the results to determine how many home games the home team have played prior to the current game (HGP) and the same for the away team (AGP). Next to this I have the totals of all games played by each team (HTT and ATT). I would like to keep the scores of each match listed between the teams as in sheet 2, but I think this will not work because of the conflict between text and numbers using INDEX/MATCH functions.

I have experimented with different SUMIFS and INDEX/MATCH functions that have either produced false results or error messages. I don't quite understand how to use INDEX/MATCH in array type formula or with the OFFSET or SMALL/LARGE function. I have been attempting to use the games played columns in formulas as a criteria to achieve my results.

Any help would be greatly appreciated.

**5foot9** · 07-22-2011, 02:02 PM

Originally Posted by 5foot9

Hi Everyone,

This is my first time here.

I have a list of football results and I'm trying to make a record of recent results (last 6 results for now) for each team next to each match (1=home win, 2= away win, 3= draw) and an overall record of home and away results for each team next to that.

I have columns next to the results to determine how many home games the home team have played prior to the current game (HGP) and the same for the away team (AGP). Next to this I have the totals of all games played by each team (HTT and ATT). I would like to keep the scores of each match listed between the teams as in sheet 2, but I think this will not work because of the conflict between text and numbers using INDEX/MATCH functions.

I have experimented with different SUMIFS and INDEX/MATCH functions that have either produced false results or error messages. I don't quite understand how to use INDEX/MATCH in array type formula or with the OFFSET or SMALL/LARGE function. I have been attempting to use the games played columns in formulas as a criteria to achieve my results.

Any help would be greatly appreciated.

Hi again,

I've managed to solve a few formula problems but I would like some help in what to do with the combined results formula and if I can have match scores betweeen the team names without causing any formula problems. I have uploaded the sheet again which includes formula this time!