How to get the last date for each 12 Mths?

[Mathman]

Hi


Would you know how I would get the last (highest day) value for each months?


IN the example for the month of January the last date is January 28 and I need to get the value next to it which is 162.


I need a formulae range that would include the entire year that gets the values for each month so I can cut and past it over other years with no need for adjustment.


Here is the example. If you can help that would be great!


Thank you

Col A	Col B	Col C	Col D
2016	Value	Mths	Results
7-Jan-12	$ 147.00	January	$ 162.00
14-Jan-12	$ 148.00	February	$ 175.00
21-Jan-12	$ 149.00	March
28-Jan-12	$ 162.00	April
4-Feb-12	$ 188.00	May
11-Feb-12	$ 195.00	June
19-Feb-12	$ 202.00	July
25-Feb-12	$ 175.00	August
3-Mar-12	$ 191.00	September
10-Mar-12	$ 200.00	October
17-Mar-12	$ 186.00	November
24-Mar-12	$ 189.00	December
30-Mar-12	$ 190.00
7-Apr-12	$ 200.00
14-Apr-12	$ 211.00
21-Apr-12	$ 205.00
28-Apr-12	$ 231.00
5-May-12	$ 232.00
12-May-12	$ 232.00
19-May-12	$ 236.00
26-May-12	$ 210.00
2-Jun-12	$ 238.00
9-Jun-12	$ 251.00
16-Jun-12	$ 266.00
23-Jun-12	$ 268.00
29-Jun-12	$ 281.00
7-Jul-12	$ 306.00
14-Jul-12	$ 326.00
22-Jul-12	$ 316.00
29-Jul-12	$ 331.00
4-Aug-12	$ 331.00
11-Aug-12	$ 318.00
18-Aug-12	$ 297.00
25-Aug-12	$ 274.00
1-Sep-12	$ 289.00
8-Sep-12	$ 284.00
16-Sep-12	$ 279.00
23-Sep-12	$ 291.00
29-Sep-12	$ 282.00
6-Oct-12	$ 259.00
13-Oct-12	$ 256.00
20-Oct-12	$ 278.00
28-Oct-12	$ 259.00
3-Nov-12	$ 259.00
10-Nov-12	$ 197.00
17-Nov-12	$ 203.00
24-Nov-12	$ 199.00
1-Dec-12	$ 209.00
8-Dec-12	$ 225.00
15-Dec-12	$ 202.00
22-Dec-12	$ 217.00
29-Dec-12	$ 233.00

<tbody>
</tbody>

 

[wideboydixon]

Book1
	A	B	C	D
1	2016	Value	Mths	Results
2	07-Jan-12	$ 147.00	January	$ 162.00
3	14-Jan-12	$ 148.00	February	$ 175.00
4	21-Jan-12	$ 149.00	March	$ 190.00
5	28-Jan-12	$ 162.00	April	$ 231.00
6	04-Feb-12	$ 188.00	May	$ 210.00
7	11-Feb-12	$ 195.00	June	$ 281.00
8	19-Feb-12	$ 202.00	July	$ 331.00
9	25-Feb-12	$ 175.00	August	$ 274.00
10	03-Mar-12	$ 191.00	September	$ 282.00
11	10-Mar-12	$ 200.00	October	$ 259.00
12	17-Mar-12	$ 186.00	November	$ 199.00
13	24-Mar-12	$ 189.00	December	$ 233.00
14	30-Mar-12	$ 190.00
15	07-Apr-12	$ 200.00
16	14-Apr-12	$ 211.00
17	21-Apr-12	$ 205.00
18	28-Apr-12	$ 231.00
19	05-May-12	$ 232.00
20	12-May-12	$ 232.00
21	19-May-12	$ 236.00
22	26-May-12	$ 210.00
23	02-Jun-12	$ 238.00
24	09-Jun-12	$ 251.00
25	16-Jun-12	$ 266.00
26	23-Jun-12	$ 268.00
27	29-Jun-12	$ 281.00
28	07-Jul-12	$ 306.00
29	14-Jul-12	$ 326.00
30	22-Jul-12	$ 316.00
31	29-Jul-12	$ 331.00
32	04-Aug-12	$ 331.00
33	11-Aug-12	$ 318.00
34	18-Aug-12	$ 297.00
35	25-Aug-12	$ 274.00
36	01-Sep-12	$ 289.00
37	08-Sep-12	$ 284.00
38	16-Sep-12	$ 279.00
39	23-Sep-12	$ 291.00
40	29-Sep-12	$ 282.00
41	06-Oct-12	$ 259.00
42	13-Oct-12	$ 256.00
43	20-Oct-12	$ 278.00
44	28-Oct-12	$ 259.00
45	03-Nov-12	$ 259.00
46	10-Nov-12	$ 197.00
47	17-Nov-12	$ 203.00
48	24-Nov-12	$ 199.00
49	01-Dec-12	$ 209.00
50	08-Dec-12	$ 225.00
51	15-Dec-12	$ 202.00
52	22-Dec-12	$ 217.00
53	29-Dec-12	$ 233.00
Sheet1

Cell Formulas
Range	Formula
D2	{=INDEX($B$2:$B$53,MAX(IF(MONTH($A$2:$A$53)=ROWS($D$2:$D2),ROW($A$2:$A$53)-ROW($A$2)+1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Mathman]

It works great, thank you very much for your help.

Regards,
MM