converting weekly rankings into monthly rankings

I have been struggling to convert an array of weekly data rankings into monthly rankings using the closest date to month end and cant seem to find a way. see below
these are a series of stock price performance rankings currently weekly. i want to take the weekly close ranking closest to the end of the month and convert them to a monthly format. ie the weekly numbers that are boxed would be inserted into the right hand column. so for example on the 28 october on the left hand table - that is the closest date to the end of october so take the data into the right hand side as the month end data ranks 1,3,2 aross the data. hope you can help with a formula. if too hard for the closest date to month end the first after month end would be ok . many thanks


spx index spgsci Index ief us equity spx index spgsci Index ief us equity

24/01/2012 1 2 4 31/12/2011 1 4 3
20/01/2012 1 3 4 30/11/2011 1 3 2
13/01/2012 1 3 4 31/10/2011 1 3 2
6/01/2012 1 3 4
30/12/2011 1 4 3
23/12/2011 1 4 3
16/12/2011 1 4 2
9/12/2011 1 3 2 how do I convert them to a monthly rank
2/12/2011 1 3 2 using the closest to month end date
25/11/2011 1 3 2
18/11/2011 1 3 2
11/11/2011 1 3 2
4/11/2011 1 3 2
28/10/2011 1 3 2
21/10/2011 1 3 2

spx indexspgsci Index ief us equity spx index spgsci Indexief us equity

24/01/2012 1 2 4 31/12/2011 1 4 3
20/01/2012 1 3 4 30/11/2011 1 3 2
13/01/2012 1 3 4 31/10/2011 1 3 2
6/01/2012 1 3 4
30/12/2011 1 4 3
23/12/2011 1 4 3
16/12/2011 1 4 2
9/12/2011 1 3 2 how do I convert them to a monthly rank
2/12/2011 1 3 2 using the closest to month end date
25/11/2011 1 3 2
18/11/2011 1 3 2
11/11/2011 1 3 2
4/11/2011 1 3 2
28/10/2011 1 3 2


this is how it should look - need the conversion formula to grabn the closest date to the end of the month and insert it in teh right hand side

excel sheet enclosed

when I tried to run the formual and adapt it to my actual spreadsheet i got an error message #N/A ?? I cant figure it out and have included teh entire worksheet and how it should look