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.
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)