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

S

stevekho

New Member
Joined
May 5, 2011
Messages
29
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 -[TABLE="width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Nothing
[/TD]
[TD]Number
[/TD]
[/TR]
[TR]
[TD]7 jan 2001
[/TD]
[TD][/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]14 Jan 2001
[/TD]
[TD][/TD]
[TD]65
[/TD]
[/TR]
[TR]
[TD]21 Jan 2001
[/TD]
[TD][/TD]
[TD]70
[/TD]
[/TR]
[TR]
[TD]28 Jan 2001
[/TD]
[TD][/TD]
[TD]68
[/TD]
[/TR]
[TR]
[TD]4 Feb 2002
[/TD]
[TD][/TD]
[TD]70
[/TD]
[/TR]
[TR]
[TD]11 Feb 2002
[/TD]
[TD][/TD]
[TD]42
[/TD]
[/TR]
[TR]
[TD]18 Feb 2002
[/TD]
[TD][/TD]
[TD]46
[/TD]
[/TR]
[TR]
[TD]25 Feb 2002
[/TD]
[TD][/TD]
[TD]29
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 
Sort by date Sort by votes
Maybe like this?
Excel Workbook
ABCDEFG
1DateNothingNumberyearmonthcombinecount
27-jan-01502001012001011
314-jan-01652001012001012
421-jan-01702001012001013
528-jan-01682001012001014
64-feb-02702002022002021
711-feb-02422002022002022
818-feb-02462002022002023
925-feb-02292002022002024
Blad2
Cell Formulas
RangeFormula
D2=YEAR(A2)
D3=YEAR(A3)
D4=YEAR(A4)
D5=YEAR(A5)
D6=YEAR(A6)
D7=YEAR(A7)
D8=YEAR(A8)
D9=YEAR(A9)
E2=MONTH(A2)
E3=MONTH(A3)
E4=MONTH(A4)
E5=MONTH(A5)
E6=MONTH(A6)
E7=MONTH(A7)
E8=MONTH(A8)
E9=MONTH(A9)
F2=D2&TEXT(E2,"00")
F3=D3&TEXT(E3,"00")
F4=D4&TEXT(E4,"00")
F5=D5&TEXT(E5,"00")
F6=D6&TEXT(E6,"00")
F7=D7&TEXT(E7,"00")
F8=D8&TEXT(E8,"00")
F9=D9&TEXT(E9,"00")
G2=COUNTIF($F$2:$F2,F2)
G3=COUNTIF($F$2:$F3,F3)
G4=COUNTIF($F$2:$F4,F4)
G5=COUNTIF($F$2:$F5,F5)
G6=COUNTIF($F$2:$F6,F6)
G7=COUNTIF($F$2:$F7,F7)
G8=COUNTIF($F$2:$F8,F8)
G9=COUNTIF($F$2:$F9,F9)
Excel Workbook
KLM
19
20Som van NumberKolomlabels
21Rijlabels4Eindtotaal
222001016868
232002022929
24Eindtotaal9797
Blad2
 
Upvote 0
Try this where D1 and E1 have the Month\Year values.

=INDEX(C:C,MATCH(EOMONTH(DATEVALUE(D1&" 1, "&E1),0),A:A,1))
 
Upvote 0
Another approach.
Excel Workbook
ABCDEFGHIJK
1DateNothingNumberyearmonthcombinecountresult45
27-jan-0150200101200101120010168680
314-jan-0165200101200101220020229290
421-jan-0170200101200101320020313130
528-jan-01682001012001014200204181718
64-feb-0270200202200202120020522220
711-feb-0242200202200202220020626260
818-feb-02462002022002023200207313031
925-feb-0229200202200202420020835350
104-mrt-02292002032002031200209403940
1111-mrt-0211200203200203220021044440
1218-mrt-02122002032002033
1325-mrt-02132002032002034
141-apr-02142002042002041
158-apr-02152002042002042
1615-apr-02162002042002043
1722-apr-02172002042002044
1829-apr-02182002042002045
196-mei-02192002052002051
2013-mei-02202002052002052
2120-mei-02212002052002053
2227-mei-02222002052002054
Blad2
Cell Formulas
RangeFormula
D2=YEAR(A2)
D3=YEAR(A3)
D4=YEAR(A4)
D5=YEAR(A5)
D6=YEAR(A6)
D7=YEAR(A7)
D8=YEAR(A8)
D9=YEAR(A9)
D10=YEAR(A10)
D11=YEAR(A11)
D12=YEAR(A12)
D13=YEAR(A13)
D14=YEAR(A14)
D15=YEAR(A15)
D16=YEAR(A16)
D17=YEAR(A17)
D18=YEAR(A18)
D19=YEAR(A19)
D20=YEAR(A20)
D21=YEAR(A21)
D22=YEAR(A22)
E2=MONTH(A2)
E3=MONTH(A3)
E4=MONTH(A4)
E5=MONTH(A5)
E6=MONTH(A6)
E7=MONTH(A7)
E8=MONTH(A8)
E9=MONTH(A9)
E10=MONTH(A10)
E11=MONTH(A11)
E12=MONTH(A12)
E13=MONTH(A13)
E14=MONTH(A14)
E15=MONTH(A15)
E16=MONTH(A16)
E17=MONTH(A17)
E18=MONTH(A18)
E19=MONTH(A19)
E20=MONTH(A20)
E21=MONTH(A21)
E22=MONTH(A22)
F2=D2&TEXT(E2,"00")
F3=D3&TEXT(E3,"00")
F4=D4&TEXT(E4,"00")
F5=D5&TEXT(E5,"00")
F6=D6&TEXT(E6,"00")
F7=D7&TEXT(E7,"00")
F8=D8&TEXT(E8,"00")
F9=D9&TEXT(E9,"00")
F10=D10&TEXT(E10,"00")
F11=D11&TEXT(E11,"00")
F12=D12&TEXT(E12,"00")
F13=D13&TEXT(E13,"00")
F14=D14&TEXT(E14,"00")
F15=D15&TEXT(E15,"00")
F16=D16&TEXT(E16,"00")
F17=D17&TEXT(E17,"00")
F18=D18&TEXT(E18,"00")
F19=D19&TEXT(E19,"00")
F20=D20&TEXT(E20,"00")
F21=D21&TEXT(E21,"00")
F22=D22&TEXT(E22,"00")
G2=COUNTIF($F$2:$F2,F2)
G3=COUNTIF($F$2:$F3,F3)
G4=COUNTIF($F$2:$F4,F4)
G5=COUNTIF($F$2:$F5,F5)
G6=COUNTIF($F$2:$F6,F6)
G7=COUNTIF($F$2:$F7,F7)
G8=COUNTIF($F$2:$F8,F8)
G9=COUNTIF($F$2:$F9,F9)
G10=COUNTIF($F$2:$F10,F10)
G11=COUNTIF($F$2:$F11,F11)
G12=COUNTIF($F$2:$F12,F12)
G13=COUNTIF($F$2:$F13,F13)
G14=COUNTIF($F$2:$F14,F14)
G15=COUNTIF($F$2:$F15,F15)
G16=COUNTIF($F$2:$F16,F16)
G17=COUNTIF($F$2:$F17,F17)
G18=COUNTIF($F$2:$F18,F18)
G19=COUNTIF($F$2:$F19,F19)
G20=COUNTIF($F$2:$F20,F20)
G21=COUNTIF($F$2:$F21,F21)
G22=COUNTIF($F$2:$F22,F22)
I2=IF(K2=0,J2,K2)
I3=IF(K3=0,J3,K3)
I4=IF(K4=0,J4,K4)
I5=IF(K5=0,J5,K5)
I6=IF(K6=0,J6,K6)
I7=IF(K7=0,J7,K7)
I8=IF(K8=0,J8,K8)
I9=IF(K9=0,J9,K9)
I10=IF(K10=0,J10,K10)
I11=IF(K11=0,J11,K11)
J2=SUMPRODUCT(($F$2:$F$44=$H2)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
J3=SUMPRODUCT(($F$2:$F$44=$H3)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
J4=SUMPRODUCT(($F$2:$F$44=$H4)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
J5=SUMPRODUCT(($F$2:$F$44=$H5)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
J6=SUMPRODUCT(($F$2:$F$44=$H6)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
J7=SUMPRODUCT(($F$2:$F$44=$H7)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
J8=SUMPRODUCT(($F$2:$F$44=$H8)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
J9=SUMPRODUCT(($F$2:$F$44=$H9)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
J10=SUMPRODUCT(($F$2:$F$44=$H10)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
J11=SUMPRODUCT(($F$2:$F$44=$H11)*(($G$2:$G$44)=J$1)*($C$2:$C$44))
K2=SUMPRODUCT(($F$2:$F$44=$H2)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
K3=SUMPRODUCT(($F$2:$F$44=$H3)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
K4=SUMPRODUCT(($F$2:$F$44=$H4)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
K5=SUMPRODUCT(($F$2:$F$44=$H5)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
K6=SUMPRODUCT(($F$2:$F$44=$H6)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
K7=SUMPRODUCT(($F$2:$F$44=$H7)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
K8=SUMPRODUCT(($F$2:$F$44=$H8)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
K9=SUMPRODUCT(($F$2:$F$44=$H9)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
K10=SUMPRODUCT(($F$2:$F$44=$H10)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
K11=SUMPRODUCT(($F$2:$F$44=$H11)*(($G$2:$G$44)=K$1)*($C$2:$C$44))
C11=ROW()
C12=ROW()
C13=ROW()
C14=ROW()
C15=ROW()
C16=ROW()
C17=ROW()
C18=ROW()
C19=ROW()
C20=ROW()
C21=ROW()
C22=ROW()
 
Upvote 0
C2: Feb
D2: 2002

E2, control+shift+enter, not just enter:

=MAX(IF(TEXT(A2:A9,"mmm")=C2,IF(YEAR(A2:A9)=D2,A2:A9)))

Is this what you are after?
 
Upvote 0
Alpha Frog's Excellent Formula can be shortened (I think)... and Without having to use the Helper Columns D & E Tks Alpha Frog

=INDEX(C:C,MATCH(EOMONTH(A2,0),A:A,1))
 
Upvote 0
AlphaFrog said:
Try this where D1 and E1 have the Month\Year values.

=INDEX(C:C,MATCH(EOMONTH(DATEVALUE(D1&" 1, "&E1),0),A:A,1))
Click to expand...

jim may said:
Alpha Frog's Excellent Formula can be shortened (I think)... and Without having to use the Helper Columns D & E Tks Alpha Frog

=INDEX(C:C,MATCH(EOMONTH(A2,0),A:A,1))
Click to expand...

Since A2 is part of the range in column A and the OP is specifying the look up value in two parts (e.g., Feb and 2002), the following shortens a bit of the excellent formula AlphaFrog suggests:

=INDEX(C:C,MATCH(EOMONTH(D1&E1,0),A:A,1))

where D1 = Feb and E1 = 2002, for example. This requires that the dates are in ascending order. (I missed the C-range on iPad, hence that unnecessary array formula.)
 
Upvote 0
Maybe this:

Layout

[TABLE="width: 242"]
<tbody>[TR]
[TD="class: xl63, width: 54, bgcolor: transparent"]Date
[/TD]
[TD="class: xl63, width: 47, bgcolor: transparent"]Nothing
[/TD]
[TD="class: xl63, width: 52, bgcolor: transparent"]Number
[/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 63, bgcolor: transparent"]Month/Year
[/TD]
[TD="class: xl63, width: 52, bgcolor: transparent"]Result
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]07/jan/01
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]jan/01
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]68
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]14/jan/01
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]65
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]fev/02
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]29
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]21/jan/01
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]70
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]28/jan/01
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]68
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]04/fev/02
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]70
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]11/fev/02
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]42
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Month
[/TD]
[TD="class: xl63, bgcolor: transparent"]Year
[/TD]
[TD="class: xl63, bgcolor: transparent"]Result
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]18/fev/02
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]46
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2001
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]68
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]25/fev/02
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]29
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Fev
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2002
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]29
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]********
[/TD]
[TD="class: xl63, bgcolor: transparent"]*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]**
[/TD]
[TD="class: xl63, bgcolor: transparent"]***********
[/TD]
[TD="class: xl63, bgcolor: transparent"]*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[/TR]
</tbody>[/TABLE]

PS: in my Excel Fev = Feb

Formulas

Array formula - use Ctrl+Shift+Enter and not only Enter

Code: 
F2-> =VLOOKUP(MAX(IF($A$2:$A$9>=E2,IF($A$2:$A$9<=EOMONTH(E2,0),$A$2:$A$9))),$A$2:$C$9,3,0)

G8-> =VLOOKUP(MAX(IF($A$2:$A$9>=1*(E8&F8),IF($A$2:$A$9<=EOMONTH(E8&F8,0),$A$2:$A$9))),$A$2:$C$9,3,0)


Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Show a value in cell from another cell based on Month
Replies
1
Views
38
Fluff
Fluff
M
  • Solved
Indirect reference of 2 cells in "sheet1" to look up value in table "table1"
Replies
8
Views
134
Kaper
Kaper
R
  • Question Question
VBA to automate calculation
Replies
1
Views
211
HighAndWilder
H
B
  • Question Question
calculate monthly value
Replies
0
Views
434
BrutalLogiC
B
G
  • Question Question
Lookup Table/column Header based on Last Row of Data (first blank value found)
Replies
7
Views
98
Fluff
Fluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top