Forum Discussion
Last week with data reported monthly
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 ...
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)
Twifoo , it doesn't return zero for the months without any data