Hello.

I have compiled dozens of worksheets documenting NFL football standings by season (each worksheet is a season). On each worksheet, each team name is listed in column F, and each team's playoff result is listed in column T. I am creating an index sheet where I can input a team name in column A, the year in column B, and then create an index/match formula in column C to list that team's playoff finish. For example:


Team Year Playoff Finish

Buffalo Bills 1981 Lost Divisional Round


Whichever team and year I input in the first two columns, I want that to pull in that team's playoff finish from that given season based on what I have written in column T (of the given season). I need help with the index/match formula. I was able to do a formula that lists a specific season, but I need a formula that will reference whichever season I input in column B. I'm sure it involves an indirect reference, but I can't seem to get the syntax right.


Any help would be appreciated.


Also, bonus points if you can help me create a second index table listing the Super Bowl winner for each season. In each individual season, the Super Bowl winner is identified as "Super Bowl Champions" in column F. I want to be able to input the season in one column and subsequently pull in that season's champion in the adjacent column.




Again, any help with either problem would be appreciated.

As start I advice you have a single sheet for standing entry - you can start with adding Season column to one season sheet as rightmost and fill it with season year number, and then adding data from other sheet and filling the season year number after every copy step. My advice is you define this table (and all other too) as Table.

You can design a single report sheet, where you select season year number, and get standings for this year displayed (Having all data in single sheet will make this task much easier, especially when you have data entry table defined as Table. Probably you need some calculated columns in data entry table to determine which rows there in which order are read into report sheet.) - or you can simply use autofilter on data entry table instead.

Thanks. I verified with a friend of mine, and it looks like that is the way to go. Thanks again for your help!