Return a value with which combo of Lookup/Match/Index?

I have attempted several variation without success.

- In Column A, I have all football teams
- In Column B, I have the dates for entire season
- In Column C, I have the points scored on that date
- In Column D, I have the points against on that date

Example:

TEAM DATE PF PA
Alabama 9/15/13 38 7
Auburn 9/15/13 14 38
Arkansas 9/15/13 18 19
Alabama 10/4/13 28 14
Auburn 10/4/13 21 36
Alabama 10/20/13 41 0
Arkansas 10/21/13 17 21
Auburn 10/21/13 24 37

- In Column F, I have all of the teams listed (125 teams)
- In Column G through Column Z, I have all of the dates that the games were played on and want the points scored

Example:

TEAM 9/15/13 10/4/13 10/20/13 10/21/13
Alabama
Auburn
Arkansas

I would like to have the value for all of the dates. In other words, Alabama on 9/15/13 scored 37 points. There will be dates that the team doesn't play on and I'll just return a blank on those dates.


It would be preferable, but I imagine that the formula would be too complicated if I chose instead to return values only on dates the team played, skipping any date they didn't play, for example

COL F COL G COL H COL I

ALABAMA 38 28 41
AUBURN 14 21 24


Thanks for your help.

Originally Posted by erickguz

I have attempted several variation without success.

- In Column A, I have all football teams
- In Column B, I have the dates for entire season
- In Column C, I have the points scored on that date
- In Column D, I have the points against on that date

Example:

TEAM DATE PF PA
Alabama 9/15/13 38 7
Auburn 9/15/13 14 38
Arkansas 9/15/13 18 19
Alabama 10/4/13 28 14
Auburn 10/4/13 21 36
Alabama 10/20/13 41 0
Arkansas 10/21/13 17 21
Auburn 10/21/13 24 37

- In Column F, I have all of the teams listed (125 teams)
- In Column G through Column Z, I have all of the dates that the games were played on and want the points scored

Example:

TEAM 9/15/13 10/4/13 10/20/13 10/21/13
Alabama
Auburn
Arkansas

I would like to have the value for all of the dates. In other words, Alabama on 9/15/13 scored 37 points. There will be dates that the team doesn't play on and I'll just return a blank on those dates.


It would be preferable, but I imagine that the formula would be too complicated if I chose instead to return values only on dates the team played, skipping any date they didn't play, for example

COL F COL G COL H COL I

ALABAMA 38 28 41
AUBURN 14 21 24


Thanks for your help.

Not sure if this is answering your question correctly but have you thought of doing this with a pivot table using the team, dates and score fields. If set up correctly the teams listed in the first column with all date heading subsequent columns and the score shown in the table for each. As there is only one score for each combination of team and date, you could use the max score, min score, avg or sum score and still get the same result. Only thing I don't follow is the scores in your sample data don't match the scores in your suggested result but I assume this is a typo not some unique anomaly in American football. Hope I'm on the right path here.