Formula to get last value of month

Greg123

New Member
Joined
Apr 8, 2019
Messages
4
I have a worksheet like this one. I am looking for a formula that will return the last production number for each month into the table on the right. However, not every date is listed so the formula can not simply look for the last day of the month (e.g. 3/31/19) because that date might not be listed at all. Any help is appreciated!

ABCDEFG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"]Day[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] , align: center"]Production[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] "]Month[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000000]#000000[/URL] "]Production[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]01/02/19[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]01/04/19[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]38,891[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Feb[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]01/15/19[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Mar[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]01/16/19[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Apr[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]01/17/19[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]90,572[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]May[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]01/23/19[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Jun[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]01/24/19[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]147,728[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Jul[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]01/31/19[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]35,279[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]02/01/19[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]02/04/19[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Oct[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]02/05/19[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]28,165[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Nov[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]02/14/19[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]44,083[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Dec[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]02/22/19[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]02/25/19[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]28,545[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]02/26/19[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]5,329[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]02/27/19[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]02/28/19[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]03/01/19[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]19,410[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]03/04/19[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]55,347[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]03/25/19[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]72,419[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]03/26/19[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]14,509[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]03/28/19[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]03/29/19[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]04/01/19[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]12,539[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]04/02/19[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]44,113[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]04/03/19[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: center"]04/04/19[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: center"]04/05/19[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]37,608[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: center"]04/08/19[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]38,097[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Maybe something like this:
This is an array formula and must be entered with CTRL-SHIFT-ENTER. Then drag formula down.
Excel Workbook
ABCDEFG
1DateDayProductionMonthProduction
21/2/2019WedJan35,279
31/4/2019Fri38,891Feb5,329
41/15/2019TueMar14,509
51/16/2019WedApr38,097
61/17/2019Thu90,572May
71/23/2019WedJun
81/24/2019Thu147,728Jul
91/31/2019Thu35,279Aug
102/1/2019FriSep
112/4/2019MonOct
122/5/2019Tue28,165Nov
132/14/2019Thu44,083Dec
142/22/2019Fri
152/25/2019Mon28,545
162/26/2019Tue5,329
172/27/2019Wed
182/28/2019Thu
193/1/2019Fri19,410
203/4/2019Mon55,347
213/25/2019Mon72,419
223/26/2019Tue14,509
233/28/2019Thu
243/29/2019Fri
254/1/2019Mon12,539
264/2/2019Tue44,113
274/3/2019Wed
284/4/2019Thu
294/5/2019Fri37,608
304/8/2019Mon38,097
Sheet
 
Upvote 0
alternative with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]Day[/td][td=bgcolor:#5B9BD5]Production[/td][td][/td][td=bgcolor:#70AD47]Month Name[/td][td=bgcolor:#70AD47]Production[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
02/01/2019​
[/td][td=bgcolor:#DDEBF7]Wed[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]January[/td][td=bgcolor:#E2EFDA]
35279​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
04/01/2019​
[/td][td]Fri[/td][td]
38,891​
[/td][td][/td][td]February[/td][td]
5329​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
15/01/2019​
[/td][td=bgcolor:#DDEBF7]Tue[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]March[/td][td=bgcolor:#E2EFDA]
14509​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
16/01/2019​
[/td][td]Wed[/td][td][/td][td][/td][td]April[/td][td]
38097​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
17/01/2019​
[/td][td=bgcolor:#DDEBF7]Thu[/td][td=bgcolor:#DDEBF7]
90,572​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
23/01/2019​
[/td][td]Wed[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
24/01/2019​
[/td][td=bgcolor:#DDEBF7]Thu[/td][td=bgcolor:#DDEBF7]
147,728​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
31/01/2019​
[/td][td]Thu[/td][td]
35,279​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
01/02/2019​
[/td][td=bgcolor:#DDEBF7]Fri[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
04/02/2019​
[/td][td]Mon[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
05/02/2019​
[/td][td=bgcolor:#DDEBF7]Tue[/td][td=bgcolor:#DDEBF7]
28,165​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
14/02/2019​
[/td][td]Thu[/td][td]
44,083​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
22/02/2019​
[/td][td=bgcolor:#DDEBF7]Fri[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
25/02/2019​
[/td][td]Mon[/td][td]
28,545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
26/02/2019​
[/td][td=bgcolor:#DDEBF7]Tue[/td][td=bgcolor:#DDEBF7]
5,329​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
27/02/2019​
[/td][td]Wed[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
28/02/2019​
[/td][td=bgcolor:#DDEBF7]Thu[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
01/03/2019​
[/td][td]Fri[/td][td]
19,410​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
04/03/2019​
[/td][td=bgcolor:#DDEBF7]Mon[/td][td=bgcolor:#DDEBF7]
55,347​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
25/03/2019​
[/td][td]Mon[/td][td]
72,419​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
26/03/2019​
[/td][td=bgcolor:#DDEBF7]Tue[/td][td=bgcolor:#DDEBF7]
14,509​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
28/03/2019​
[/td][td]Thu[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
29/03/2019​
[/td][td=bgcolor:#DDEBF7]Fri[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
01/04/2019​
[/td][td]Mon[/td][td]
12,539​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
02/04/2019​
[/td][td=bgcolor:#DDEBF7]Tue[/td][td=bgcolor:#DDEBF7]
44,113​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
03/04/2019​
[/td][td]Wed[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
04/04/2019​
[/td][td=bgcolor:#DDEBF7]Thu[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
05/04/2019​
[/td][td]Fri[/td][td]
37,608​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
08/04/2019​
[/td][td=bgcolor:#DDEBF7]Mon[/td][td=bgcolor:#DDEBF7]
38,097​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]


Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Table.TransformColumnTypes(Source,{{"Date", type date}, {"Day", type text}, {"Production", Int64.Type}}), "Month Name", each Date.MonthName([Date]), type text),
    Group = Table.Group(Table.SelectRows(Month, each ([Production] <> null)), {"Month Name"}, {{"Max", each List.Max([Date]), type date}, {"Count", each _, type table}}),
    Expand = Table.ExpandTableColumn(Table.AddColumn(Group, "Custom", each Table.LastN([Count],1)), "Custom", {"Production"}, {"Production"}),
    ROC = Table.RemoveColumns(Expand,{"Count", "Max"})
in
    ROC[/SIZE]
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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