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