Lookup?

• Hope this isn't too basic - please excuse me - I'm a newbie.

I have a table of tennis matchs played over a few days as follows:

date - player - rating - player - rating
30/3 A ?? B ?
29/3 C 10 F 5
28/3 B 25 G 10
27/3 D 15 A 10
26/3 C 30 B 5
25/3 A 20 F 15

First I need to 'lookup' player A's most recent game and enter it in the ?? space. So I need to check both columns to see if his most recent match was in the left or right column. In this instance it's in the right (ie: 27/3 which=10) but I don't want the entry at the 25/3 as it's an older game. Vlookup and hlookup allow me to search one column but not across two. Searching for player B's new rating has similar problems as there are games across two columns. This is also a continual process. On the 31/3 (ie:next day) I need to 'lookup' the next two player ratings by going over previous results

Thanks for any help you can provide - hope the above makes sense.

• In theory, Vlookup IS a fairly easy topic to get to grips with, however, reading through your example, I'm thinking that what you are attempting to achieve is made extremely more complex due to the way that you are recording the data.

Are you able to consider changing the layout of the data ? If so, I may have some suggestions.

• well, this looked simple, but trawling through a solution, I could only provide an ugly one......

I couldn't come up with a single solution for both columns, so see the pink and green cells for a solution in each column

Will, post yours please and put me out of my misery !!!!!!!!!!:(

<CENTER&gt;<TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%&gt;<TR&gt;<TD COLSPAN=6 BGCOLOR=#0C266B &gt;<TABLE ALIGN=CENTER BORDER=0 WIDTH=100%&gt;<TR&gt;<TD ALIGN=LEFT&gt;<FONT COLOR=WHITE&gt;<B&gt;Microsoft Excel - MRICH1.xls</B&gt;</FONT&gt;</TD&gt;<TD ALIGN=RIGHT&gt;<FONT COLOR=WHITE SIZE=2&gt;___Running: xl97 : OS = Windows (32-bit) 4.90</FONT&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=#D4D0C8 COLSPAN=6&gt;<TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%&gt;<TR&gt;<TD&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp</TD&gt;<TD ALIGN=RIGHT VALIGN=MIDDLE&gt;<FORM NAME='formCb4287262003'&gt;<INPUT TYPE='Button' NAME='btCb5793506491' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb7224230428.sltNb9631429103.value);'&gt;</FORM&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=WHITE COLSPAN=6&gt;<TABLE BORDER=0&gt;<TR&gt;<Form name='formFb7224230428'&gt;<TD WIDTH=5% ALIGN=CENTER BGCOLOR=White&gt;<SELECT NAME='sltNb9631429103' onChange='document.formFb7224230428.txbFb5954886793.value = document.formFb7224230428.sltNb9631429103.value'&gt;<option value='=IF(MATCH(B2,\$B\$3:\$B\$23,0)<MATCH(B2,\$D\$3:\$D\$23,0),OFFSET(C3,MATCH(B2,\$B\$3:\$B\$23,0)-1,0,1,1),OFFSET(E3,MATCH(B2,\$D\$3:\$D\$23,0)-1,0,1,1))'&gt;C2<option value='=IF(MATCH(D2,\$D\$3:\$D\$23,0)<MATCH(D2,\$B\$3:\$B\$23,0),OFFSET(E3,MATCH(D2,\$D\$3:\$D\$23,0)-1,0,1,1),OFFSET(E3,MATCH(D2,\$B\$3:\$B\$23,0)-1,0,1,1))'&gt;E2</select&gt;</TD&gt;<TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 &gt;<B>=</B&gt;</TD&gt;<TD ALIGN=LEFT BGCOLOR=White&gt;<input type='text' name='txbFb5954886793' size='120' value='=IF(MATCH(B2,\$B\$3:\$B\$23,0)<MATCH(B2,\$D\$3:\$D\$23,0),OFFSET(C3,MATCH(B2,\$B\$3:\$B\$23,0)-1,0,1,1),OFFSET(E3,MATCH(B2,\$D\$3:\$D\$23,0)-1,0,1,1))'&gt;</TD&gt;</form&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;A</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;B</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;C</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;D</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;E</CENTER&gt;</B&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;1</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#000000 ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#FFFFFF&gt;Date</FONT&gt;</TD&gt;<TD BGCOLOR=#000000 ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#FFFFFF&gt;Player 1</FONT&gt;</TD&gt;<TD BGCOLOR=#000000 ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#FFFFFF&gt;Result</FONT&gt;</TD&gt;<TD BGCOLOR=#000000 ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#FFFFFF&gt;Player 2</FONT&gt;</TD&gt;<TD BGCOLOR=#000000 ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#FFFFFF&gt;Result</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;2</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;30/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;D</FONT&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=IF(MATCH(B2,\$B\$3:\$B\$23,0)%3CMATCH(B2,\$D\$3:\$D\$23,0),OFFSET(C3,MATCH(B2,\$B\$3:\$B\$23,0)-1,0,1,1),OFFSET(E3,MATCH(B2,\$D\$3:\$D\$23,0)-1,0,1,1))')&gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;E</FONT&gt;</TD&gt;<TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=IF(MATCH(D2,\$D\$3:\$D\$23,0)%3CMATCH(D2,\$B\$3:\$B\$23,0),OFFSET(E3,MATCH(D2,\$D\$3:\$D\$23,0)-1,0,1,1),OFFSET(E3,MATCH(D2,\$B\$3:\$B\$23,0)-1,0,1,1))')&gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;3</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;30/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;B</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;A</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;4</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;29/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;C</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;B</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;5</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;29/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;A</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;C</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;6</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;28/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;A</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;D</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;7</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;28/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;B</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;E</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;8</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;27/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;C</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;A</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;9</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;27/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;D</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;B</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;10</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;26/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;E</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;C</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;11</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;26/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;B</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;D</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;12</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;C</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;E</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;13</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;D</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;A</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;14</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;A</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;B</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;15</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;B</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;C</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;16</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;C</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;D</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;17</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;D</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;E</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;18</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;22/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;A</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;E</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;19</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;22/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;B</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;D</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;20</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;04/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;C</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;E</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;25</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;21</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;04/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;D</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;E</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;20</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;22</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;03/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;A</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;E</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;15</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;23</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;03/03/2003</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;B</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;A</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10</FONT&gt;</TD&gt;</TR&gt;<TR&gt;<TD COLSPAN=6&gt;<U&gt;Sheet1</U&gt;</TD&gt;</TR&gt;</TABLE&gt;<BR&gt;<FONT COLOR=#339966&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT COLOR=RED SIZE=2&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs.</FONT&gt;<BR&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;The above image was automatically generated by [HtmlMaker [email protected]]</FONT&gt;<FONT COLOR=#339966 SIZE=1&gt;If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm&gt;click here</A&gt; to download</FONT&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]&gt;Ivan F Moala</A&gt; All credit to <A HREF=mailto:[email protected]&gt;Colo</A&gt;</FONT&gt;<BR&gt;</CENTER&gt;

• Chris and Will

Thanks so much for your replies.
Chris - the formula may be ugly but it 's effective so cheers.
Will - the data has been given to me in the illustrated form (with several other coulmns involved as well). More than 17000 games so I'm not sure how easy it would be to change this data but open to any suggestions. I can imagine if the data could be edited and players placed above each other in one single coulmn then it would simply be a case of Vlookup (in other words, interlaced together)

Thanks once again for your input. Very grateful.

• an alternative....

assumptions:

1) new records are added to the data by inserting new rows at the top.
2) a player can only appear once per row
3) the most recent entry = the one closest to the top of the list in either column.

Intro:
With the data as set up by Chris:

=IF(MATCH(C2,C3:C23,0)< MATCH(C2,E3:E23),VLOOKUP(C2,E3:F23,2,0),VLOOKUP(C2,C3:D23,2,0))

is the formula for D2. This is conceptually similar to Chris's formula, but doesn't call the volatile offset(), which could give performance probs if used often.

This issue now is to make this formula sensitive to the fact that the table is growing. This is achieved by setting up dynamic named ranges:

LeftTable is defined as follows (in insert | name | define):

=INDEX(Sheet1!\$C:\$C,3,1):INDEX(Sheet1!\$D:\$D,MATCH(Bignum,Sheet1!\$D:\$D))

where Bignum is defined as:

=9.99999E307

RightTable is defined equivalently as:

=INDEX(Sheet1!\$E:\$E,3,1):INDEX(Sheet1!\$F:\$F,MATCH(Bignum,Sheet1!\$F:\$F))

See the example:

the first formula is now:

=IF(MATCH(C2,\$C:\$C,0)< MATCH(C2,\$E:\$E),VLOOKUP(C2,RightTable,2,0),VLOOKUP(C2,LeftTable,2,0))