Last week with data reported monthly

[kfb041720144]

So I have a sheet that is updated weekly. In version 1 tab, cell a1, this is where I manually input the date. From there, cells A6-A53, it dates back two years (Sep -17 cell A6) and goes forward two years (Aug 21-A53) on a monthly basis.  Version 2 tab is the same thing but I wanted to show that the dates change.

Sheet 5 reflects the data that is manually input BY WEEK. I put in data every week and at the end of each month, I use the last week of data. For example, Sheet 5 for Sept 2017 cells A376-A380, I have 5 weeks of data Sept 1st, Sept 8th, Sept 15th,Sept 22nd, and Sept 29th. So for Sep 2017 that is reflected on version 1's tab cell a6 , I would like to use the last week of data which is the 29th of Sept 3552. So in hindisght, I would like to pull the last week of data.

However, b/c I am updating and reporting this information weekly, sometimes the monthly data is not completely full and I would have to use the last week of data that was reported until the data is filled in monthly. This is the area that I am having trouble in. 

First, I need a formula that would pull the last week of the monthly data and if that is not available(if blank) use the week of data that is available. For example, sheet 5 cell A476, the only available data is the 2-aug-19 and I would like that data to be pulled instead of the blank data for 30-Aug-19. 

Second, the formula that I tried to use, it is pulling the first week of data instead of the last week of data. I have highlighted the areas of concern and I have input comments. 

Sorry for the long post. Your response will be greatly appreciated. 

[#2]

@kfb041720144 

That could be like

=(EOMONTH(TODAY(),0)+1>=A6)*LOOKUP(2,1/(Sheet5!C:C>0)/(A6>=Sheet5!A:A),Sheet5!C:C)

[#3]

@kfb041720144 

Your VLOOKUP retrieves the value of the last date that is equal (or less than) the first day of each month.

Your omission of the range_lookup argument defaults to TRUE (or Approximate match), which is the reason for the "equal (or less than)" part of my foregoing statement. 

Also, your lookup_value argument is the first day of each month, as I stated above. The formula you need must retrieve the value of the last date that is equal (or less than) the last day of each month. Such formula in Version 1!D6 of the attached file is: 

=LOOKUP(2,1/(
(Sheet5!A:A<=EOMONTH(A6,0))*(Sheet5!C:C<>"")),
Sheet5!C:C)

[#4]

@Twifoo , it doesn't return zero for the months without any data