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>

 

[bluegold]

ok so how would I change the "no prior results" to a non-numeric value, say "nd" & then the win, loss & draw to w, l, d00.

 

[DonkeyOte]

OK, so I can see where the confusion came from (I posted a different formula earlier by mistake!)

L2:
=IF(COUNTIF($B$1:$C1,B2)=0,"nd",ROUND(MOD(MAX(IF($B$1:$C1=B2,ROW($B$1:$B1)+$Q$1:$R1/1000)),1)*1000,0)-ROUND(MOD(MAX(IF($B$1:$C1=B2,ROW($B$1:$B1)+IF($B$1:$B1=B2,$R$1:$R1,$Q$1:$Q1)/1000)),1)*1000,0))
confirmed with CTRL + SHIFT + ENTER
applied to L2:M17

same custom number format as outlined earlier

 

[bluegold]

Yeah that's it How the hell do make formula's look so easy?

 

[bluegold]

To get same "nd" result with column J & K with win / loss streak's - how would I modify the formula below.

Spreadsheet Formulas <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>

 

[DonkeyOte]

Same premise really - pre-emptive COUNTIF - this in turn allows you to simplify the LOOKUP (removing the CHOOSE which acts as an error handler)

J2:
=IF(COUNTIF($B$1:$C1,B2)=0,"nd",LOOKUP(9.99E+307,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)))
copied across J2:K17

NOTE:

To use the above you will also need to adjust V & W to account for the fact that J & K now contain a mix of numbers & strings (previously just numbers)

V2:
=IF(P2,SUM(P2,N(J2)*(SIGN(P2)=SIGN(INT(N(J2))))),(HOUR(ABS(N(J2)))+1)/22)
copied down to V17

W2:
=IF(U2,SUM(U2,N(K2)*(SIGN(U2)=SIGN(INT(N(K2))))),(HOUR(ABS(N(K2)))+1)/22)
copied down to W17

If memory serves correctly the use of Hour etc is to keep a running total of draws.

 

[bluegold]

Yep works a treat. Thanks DonkeyOte for your help once again