Spreadsheet: 75922d1276531161-excel-2007-looking-for-formulas-to-determine-high-low-position-sample
Sheet: Sample

Elite Ice Hockey League
Team Standings per game day
Week |  |  | 1 |  | 2 |  | 3 |  | 4 |  |  | 5
Day |  |  | Sa | Su | Sa | Su | Sa | Su | [NAME] | Sa | Su | We
Game Day |  |  | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10
Teams | High | Low | 2010-09-04 00:00:00 | 2010-09-05 00:00:00 | 2010-09-11 00:00:00 | 2010-09-12 00:00:00 | 2010-09-18 00:00:00 | 2010-09-19 00:00:00 | 2010-09-21 00:00:00 | 2010-09-25 00:00:00 | 2010-09-26 00:00:00 | 2010-09-29 00:00:00
Belfast |  |  | 9 | 8 | 9 | 9 | 9 | 9 | 10 | 10 | 10 | 10 |  | Week 5 - Game day 10
Braehead |  |  | 4 | 4 | 3 | 2 | 2 | 2 | 1 | 1 | 1 | 1 |  |  |  | GP | Wins | Lost | REG/W | OTL | PSL | GF | GA | PTS
Cardiff |  |  | 5 | 9 | 7 | 8 | 8 | 7 | 7 | 7 | 7 | 8 |  | 1 | Braehead | 54 | 41 | 13 | 35 | 2 | 5 | 201 | 115
Coventry |  |  | 10 | 5 | 6 | 7 | 7 | 6 | 4 | 4 | 4 | 4 |  | 2 | [NAME] | 54 | 38 | 16 | 34 | 2 | 0 | 228 | 151
Dundee |  |  | 1 | 3 | 5 | 6 | 6 | 8 | 8 | 9 | 9 | 7 |  | 3 | Hull | 54 | 37 | 17 | 33 | 1 | 3 | 225 | 153
Edinburgh |  |  | 6 | 10 | 10 | 10 | 10 | 10 | 9 | 8 | 8 | 9 |  | 4 | Coventry | 54 | 35 | 19 | 29 | 3 | 1 | 216 | 170
Hull |  |  | 2 | 2 | 2 | 4 | 4 | 3 | 3 | 3 | 3 | 3 |  | 5 | Sheffield | 54 | 28 | 26 | 22 | 2 | 3 | 172 | 141
Newcastle |  |  | 7 | 7 | 8 | 5 | 5 | 4 | 5 | 5 | 5 | 6 |  | 6 | Newcastle | 54 | 26 | 21 | 22 | 6 | 2 | 198 | 179
[NAME] |  |  | 3 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 |  | 7 | Dundee | 54 | 22 | 32 | 14 | 0 | 4 | 146 | 183
Sheffield |  |  | 8 | 6 | 4 | 3 | 3 | 5 | 6 | 6 | 6 | 5 |  | 8 | Cardiff | 54 | 21 | 33 | 18 | 3 | 3 | 179 | 243
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 9 | Edinburgh | 54 | 21 | 33 | 13 | 3 | 2 | 154 | 243
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 10 | Belfast | 54 | 8 | 46 | 5 | 2 | 1 | 131 | 272
In week 9 the positions were as follows  Belfast 10th, Braehead 1st, Cardiff 7th, [NAME], Dundee 9th, Edinburgh 8th, Hull 3rd, Newcastle 5th, Nottingham 2nd and Sheffield 6th. As can be seen on the table to the left. Then in week 10 Belfast are still 10th, Braehead are still 1st, Cardiff have moved down from 7th to 8th, Coventry are still 4th, Dundee have moved from 9th to 7th, [NAME] are still 3rd, Newcastle have moved from 5th down to 6th, [NAME] are still 2nd and Sheffield have moved up from 6th to 5th.
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Week 4 - Game Day 9
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | GP | Wins | Lost | REG/W | OTL | PSL | GF | GA | PTS
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 | Braehead | 54 | 41 | 13 | 35 | 2 | 5 | 201 | 115
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 | [NAME] | 54 | 38 | 16 | 34 | 2 | 0 | 228 | 151
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 3 | Hull | 54 | 37 | 17 | 33 | 1 | 3 | 225 | 153
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 4 | Coventry | 54 | 35 | 19 | 29 | 3 | 1 | 216 | 170
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 5 | Newcastle | 28 | 26 | 21 | 22 | 6 | 2 | 141 | 179
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 6 | Sheffield | 54 | 28 | 26 | 22 | 2 | 2 | 198 | 179
What I want to be able to do is when the league positions change - the relevant cell above changes to correspond with the teams position. For example how can I tell cell M9 that the team is now 8th rather than 7th from the previous week. |  |  |  |  |  |  |  |  |  |  |  |  |  | 7 | Cardiff | 54 | 22 | 32 | 14 | 0 | 4 | 146 | 183
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 8 | Edinburgh | 54 | 21 | 33 | 18 | 3 | 2 | 179 | 243
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 9 | Dundee | 54 | 21 | 33 | 13 | 3 | 2 | 154 | 243
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 10 | Belfast | 54 | 8 | 46 | 5 | 2 | 1 | 131 | 272