Return value of the last week of the month based on the year and month

[stevekho2]

Hello,

I have a problem and i dont really know how to fix it. I have a column with some dates all distanced by one week -

Date Number
7 jan 2001 50
14 Jan 2001 65
21 Jan 2001 70
28 Jan 2001 68
4 Feb 2002 70
11 Feb 2002 42
18 Feb 2002 46
25 Feb 2002 29


I would like a formula that based on the month and year will return the last value it can find for this month and year - So if we have Jan & 2001 it will return 68 (last week), Feb & 2001, it will return 29 (last week).
The month could sometime be 4 or 5 weeks long so it is really the last value of the month.

Thanks so much for your help

[martindwilson]

those dates leap a year between jan and feb is that correct?

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1		-----	-----		-----
2	7-Jan-2001	50		Jan 2001	68
3	14-Jan-2001	65		Feb 2002	29
4	21-Jan-2001	70
5	28-Jan-2001	68
6	4-Feb-2002	70
7	11-Feb-2002	42
8	18-Feb-2002	46
9	25-Feb-2002	29

D2:D3 = month year as a TEXT entry

Enter this formula in E2 and copy down:

=LOOKUP(2,1/(TEXT(A$2:A$9,"mmm yyyy")=D2),B$2:B$9)

[contaminated]

Hello

as an option, u can use array formula confirmed by Control + Shift + Enter

Please Login or Register  to view this content.

ps: sample is attachedReturn value of the last week of the month based on the year and month.xlsx

[jeffreybrown]

Your post does not comply with Rule 8 of our Forum RULES.

Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[martindwilson]

or
=LOOKUP(2,1/(TEXT(A$2:A$20,"mmmyyyy")=E1&F1),B$2:B$20) with jan in e1 2001 in f1
or
=LOOKUP(EOMONTH(DATE(F1,MONTH(DATEVALUE("01-"&E1)),1),0),A1:B20)

[MarvinP]

Hi Steve and welcome to the forum,

Your answer is a combination of understanding VLookup with a TRUE as the last argument and finding the last day of a month.

Look at http://www.contextures.com/xlFunctions02.html and see if you see why a TRUE in a VLookup will "back up one" when looking down your date column.

[stevekho2]

Sorry about the cross point guys, it wont happen again
Thanks for all your help! I think I got it!