Formula to display previous result?

[bluegold]

I need a formula to calculate a team's last game result - i.e Column L & M
Where column L is the Favourite team's last result and column M is the non-favourite team's last result. I have manually entered the answers to show the format I want results shown as. The previous game's scores are located in column's Q & R (For & Against). I'm guessing helper columns will be required. Any idea's as I'm stumped!

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 14px;"> <col style="width: 73px;"> <col style="width: 73px;"> <col style="width: 48px;"> <col style="width: 38px;"> <col style="width: 45px;"> <col style="width: 31px;"> <col style="width: 44px;"> <col style="width: 46px;"> <col style="width: 39px;"> <col style="width: 39px;"> <col style="width: 32px;"> <col style="width: 32px;"> <col style="width: 45px;"> <col style="width: 38px;"> <col style="width: 33px;"> <col style="width: 28px;"> <col style="width: 28px;"> <col style="width: 45px;"> <col style="width: 45px;"> <col style="width: 67px;"> <col style="width: 86px;"> <col style="width: 94px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td> <td>N</td> <td>O</td> <td>P</td> <td>Q</td> <td>R</td> <td>S</td> <td>T</td> <td>U</td> <td>V</td> <td>W</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">=</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">FAV</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">N-FAV</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">DATE</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">WIN</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">LOSS</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">RND</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">TABLE</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">%CH</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">W/L</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">W/L 2</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">Last</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">Last</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt; font-weight: bold;">L$</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt; font-weight: bold;">W$</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">RES</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">F</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">A</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">W2</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">L2</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">N-FAV RES</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">CURRENT W/L</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">CURRENT W/L2</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">gcoast</td> <td style="text-align: left; font-size: 9pt;">nqld</td> <td style="text-align: left; font-size: 9pt;">14/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.62</td> <td style="text-align: left; font-size: 9pt;">$2.30</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.62</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">36</td> <td style="text-align: left; font-size: 9pt;">18</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">sydney</td> <td style="text-align: left; font-size: 9pt;">souths</td> <td style="text-align: left; font-size: 9pt;">14/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.85</td> <td style="text-align: left; font-size: 9pt;">$1.95</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.85</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">34</td> <td style="text-align: left; font-size: 9pt;">20</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">manly</td> <td style="text-align: left; font-size: 9pt;">cronulla</td> <td style="text-align: left; font-size: 9pt;">15/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.45</td> <td style="text-align: left; font-size: 9pt;">$2.75</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.75</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">10</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">newcastle</td> <td style="text-align: left; font-size: 9pt;">canberra</td> <td style="text-align: left; font-size: 9pt;">15/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.55</td> <td style="text-align: left; font-size: 9pt;">$2.45</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.55</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">30</td> <td style="text-align: left; font-size: 9pt;">14</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">parramatta</td> <td style="text-align: left; font-size: 9pt;">canterbury</td> <td style="text-align: left; font-size: 9pt;">15/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.48</td> <td style="text-align: left; font-size: 9pt;">$2.65</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.48</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">28</td> <td style="text-align: left; font-size: 9pt;">20</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">brisbane</td> <td style="text-align: left; font-size: 9pt;">penrith</td> <td style="text-align: left; font-size: 9pt;">16/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.43</td> <td style="text-align: left; font-size: 9pt;">$2.85</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.43</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">48</td> <td style="text-align: left; font-size: 9pt;">12</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">sgeorge</td> <td style="text-align: left; font-size: 9pt;">wests</td> <td style="text-align: left; font-size: 9pt;">16/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.75</td> <td style="text-align: left; font-size: 9pt;">$2.08</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$1.00</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.00</td> <td style="text-align: left;">draw</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">melbourne</td> <td style="text-align: left; font-size: 9pt;">nzealand</td> <td style="text-align: left; font-size: 9pt;">17/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.28</td> <td style="text-align: left; font-size: 9pt;">$3.70</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.28</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">32</td> <td style="text-align: left; font-size: 9pt;">18</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">canterbury</td> <td style="text-align: left; font-size: 9pt;">souths</td> <td style="text-align: left; font-size: 9pt;">21/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.82</td> <td style="text-align: left; font-size: 9pt;">$2.00</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">l08</td> <td style="text-align: left; font-size: 9pt;">l14</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.82</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">25</td> <td style="text-align: left; font-size: 9pt;">12</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">sydney</td> <td style="text-align: left; font-size: 9pt;">brisbane</td> <td style="text-align: left; font-size: 9pt;">21/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.68</td> <td style="text-align: left; font-size: 9pt;">$2.20</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">w14</td> <td style="text-align: left; font-size: 9pt;">w36</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.20</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">14</td> <td style="text-align: left; font-size: 9pt;">20</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">manly</td> <td style="text-align: left; font-size: 9pt;">newcastle</td> <td style="text-align: left; font-size: 9pt;">22/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.60</td> <td style="text-align: left; font-size: 9pt;">$2.35</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">l06</td> <td style="text-align: left; font-size: 9pt;">w16</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.35</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">12</td> <td style="text-align: left; font-size: 9pt;">13</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l02</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">nqld</td> <td style="text-align: left; font-size: 9pt;">wests</td> <td style="text-align: left; font-size: 9pt;">22/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.33</td> <td style="text-align: left; font-size: 9pt;">$3.35</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">l18</td> <td style="text-align: left; font-size: 9pt;">d16</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$3.35</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">10</td> <td style="text-align: left; font-size: 9pt;">30</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l02</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">penrith</td> <td style="text-align: left; font-size: 9pt;">canberra</td> <td style="text-align: left; font-size: 9pt;">22/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.58</td> <td style="text-align: left; font-size: 9pt;">$2.40</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">l36</td> <td style="text-align: left; font-size: 9pt;">l16</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.40</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="text-align: left; font-size: 9pt;">20</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l02</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">melbourne</td> <td style="text-align: left; font-size: 9pt;">cronulla</td> <td style="text-align: left; font-size: 9pt;">23/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.36</td> <td style="text-align: left; font-size: 9pt;">$3.20</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">w14</td> <td style="text-align: left; font-size: 9pt;">w06</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$3.20</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="text-align: left; font-size: 9pt;">17</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">parramatta</td> <td style="text-align: left; font-size: 9pt;">nzealand</td> <td style="text-align: left; font-size: 9pt;">23/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.82</td> <td style="text-align: left; font-size: 9pt;">$2.00</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">w08</td> <td style="text-align: left; font-size: 9pt;">l14</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.00</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="text-align: left; font-size: 9pt;">30</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">sgeorge</td> <td style="text-align: left; font-size: 9pt;">gcoast</td> <td style="text-align: left; font-size: 9pt;">24/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.65</td> <td style="text-align: left; font-size: 9pt;">$2.25</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">d16</td> <td style="text-align: left; font-size: 9pt;">w18</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.65</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">30</td> <td style="text-align: left; font-size: 9pt;">12</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr></tbody></table>

 

[Aladin Akyurek]

Can you explain how d00 in L2 obtains from the F data in column Q?

 

[bluegold]

"d00" is displayed because there isn't any previous results to display. Basically the first round of matches (first 8 rows) will show d00 to start with. But all matches after the first round of matches will have previous matches to refer to. Also note a draw is displayed as the points scored preceeded by a "d" instead of "w" or "l" as shown in column M13.

 

[energman58]

It would be a lot easier if you did it the other way up - latest at the top as you will probably need to use MATCH or a lookup to find your team's last game and MATCH works down the column only

So if your column looks like this

1 Jan Manly
12 Dec St George
11 Dec Penrith
5 Dec Manly

You can find the last game simply by looking down using MATCH in both your FAV and Non FAV columns and picking the smaller of the two values but if it is the other way up you wont be able to do it and you will have to search using VBA if you search upwards

Would that be a problem?

 

[bluegold]

I prefer it to be chronological (oldest games first). Because that way I can add games to the spreadsheet in sequence. Not sure if this is any help but I can show you the formula to calculate win / loss streak's for Favourite & non-favourite teams as shown in column's J & K.

<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>J2</td> <td>=LOOKUP(9.99E+307,CHOOSE({1,2},0,INDIRECT("R"&SUBSTITUTE(LOOKUP(9.99E+307,SIGN(SEARCH("@"&B2&"@","@"&$B$1:$B1&"@"&$C$1:$C1&"@"))*ROW($B$1:$B1)+0.022+(0.001*($C$1:$C1=B2))),".","C"),FALSE)))</td></tr> <tr> <td>K2</td> <td>=LOOKUP(9.99E+307,CHOOSE({1,2},0,INDIRECT("R"&SUBSTITUTE(LOOKUP(9.99E+307,SIGN(SEARCH("@"&C2&"@","@"&$B$1:$B1&"@"&$C$1:$C1&"@"))*ROW($B$1:$B1)+0.022+(0.001*($C$1:$C1=C2))),".","C"),FALSE)))</td></tr></tbody></table></td></tr></tbody></table>
Helper columns for above:

<table border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>U2</td> <td>=P2*-1</td></tr> <tr> <td>V2</td> <td>=IF(P2,SUM(P2,J2*(SIGN(P2)=SIGN(INT(J2)))),(HOUR(ABS(J2))+1)/22)</td></tr> <tr> <td>W2</td> <td>=IF(U2,SUM(U2,K2*(SIGN(U2)=SIGN(INT(K2)))),(HOUR(ABS(K2))+1)/22)</td></tr></tbody></table>

 

[bluegold]

Anyone?

 

[energman58]

I think this is the first part of the formula for L17 - I am sure you can extend it as appropriate - where it says "needs more" here you need to replicate the first part of the formula to account for the case where the team is the non favourite - (the second part of the first if) so all the references to the B2:B16 become references to C and you need to turn the greater than and less then formulas in the fiirst part around and multiply the points differences by -1.

I said it would be MUCH easier if it was the other way up - most of teh complexity involves finding the most recent game
<?XML:NAMESPACE PREFIX = INDEX(Q1 /><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>The expression is so long the code tags dont work!</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>So here it is in bits</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>=IF(SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16)))>SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),IF(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)>INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>"w"&INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)-INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),IF(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1),<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),< INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16='B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need'></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>"Need More Here")</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>There will be a slightly less complex expression using array formulas (maybe) or helper columns using the same logic - I have not tried to produce something where the team has not played before - probably you need to capture that too!</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>Good luck
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),<></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>

 

[bluegold]

Thanks for your formula energman58. Unfortunately I'm a novice excel user at best let alone a formula guru so I wouldn't know how to modify your formula to produce the correct results like returning a d00 when no prior results avail etc. Sounds like an array with helper columns might be the go - like was done in a previous column's formula I displayed.

 

[energman58]

If you want to persevere with the big formula my version is almost there - I just got bored with wrangling it! It is just me and teh dog at home tonight so I may have another try.

However, I would probably split it up to find the most recent match for the team and then separately determine if it is a win or a loss etc.

 

[energman58]

here is the complete formula for L17

You need to fix it for M17 but you can do most of it by dragging

As it is it works provided that both teams have played previously - it obvioulsy falls over if they have not

=IF(SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16)))>SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),IF(INDEX($Q$1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)>INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)

*ROW($B$2:B16))),2),"w"&INDEX($Q$1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)-INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),IF(INDEX($Q$1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)<INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)

*ROW($B$2:B16))*R17))-(INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),IF(INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),2)>INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)

*ROW($C$2:C16))),1),"w"&INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),2)-INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),1),IF(INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),2)

<INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),1),"l"&(INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))*R17),1)-(INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),2))),"d"&(INDEX($Q$1:Q17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),1)))))