Find Last Occurance of Given Month & Year in Range

[talt0612]

Hi all- I'm stuck on a problem. For a given date, how do I find the last occurance of the given dates' month and year in a range?

Here are some more details:
A1 =a random date in the format "MM/DD/YYYY".
Column B = range of dates from 1/1/2009 - today in format "MM/DD/YYYY"
Column C = a transaction value corresponding to the date in B.

The dates are in column B are in chronological order, but not all dates are listed. So for instance B1 might be 1/1/2009, B2 is 1/2/2009, but B3 is 1/7/2009 because there were no transactions from 1/3/2009 to 1/6/2009.

So if A1 is 1/1/2009, I need to look in Column B for the last transaction date in the month of Jan 2009 and return the value in column C.

Any thoughts on how I can do this in VBA. I'm using Excel 2007.

Thanks very much in advance.

 

[Jonmo1]

Doh!....
My bad...

Change Edate to EOMONTH

 

[Jonmo1]

is it Monday again already??
I had the logic in my head, it just got lost somewhere between there and the keyboard...

Yes, that formula looks MUCH better.

sorry, reverted to R1C1 here....B2= B1+3, B3=B2+3....

What if I modify the formula to :

=LOOKUP(EOMONTH(A1,0),B:C)

This seems to work even when the last two dates in the range are the same (i.e. today), but I'm not sure how, why.