Latest Date with data by month

[kb041714]

I have data that is listed weekly and I have to graph them into monthly reports. My problem is that I want to write a formula that takes the latest week with data by month. For example I have 4 weeks of data from august and 2 weeks of data from sept. for my value column (see attached), I want a formula that takes the last week of August data and the 2nd week of Sept data. I have attached a sheet for your review. Your response will be greatly appreciated. 

[#2]

HIi @kb041714 

It is good practice to store your data in an Excel Table (in the ribbon, Insert tab, and then Table)

Then you can easily reference your table and its column in your function. That way your formula will remain dynamic as your table expands.

You could then use a VLOOKUP() function like this

=VLOOKUP(EOMONTH(D2,0),Table1,2,TRUE)

Or equally, use INDEX() and MATCH() like this

=INDEX(Table1,MATCH(EOMONTH(D2,0),Table1[Date],1),2)

I am assuming that in cell D2 you have a date that contains the same month and same year than the month from which you are trying to retrieve the last value.

Does that correspond to what you are looking for?

[#3]

For D2:
=LOOKUP(2,1/
(MONTH(A$2:A$9)=8),
B$2:B$9)
For D3:
=LOOKUP(2,1/(
A$2:A$9=AGGREGATE(15,6,
1/(MONTH(A$2:A$9)=9)*
A$2:A$9,2)),
A$2:A$9)
For E2, copied down to E3:
=LOOKUP(2,1/
(A$2:A$9=D2),
B$2:B$9)