Which formula to look for change in column and return value from another column/cell

[Want2BExcel]

I can't figure out a way to get this to work.
I need a formula for each month A2:L2 which gives me a value from the SERIAL NO column in the DataSheet. The criteria is always the last cell in each month (For demo I have shown in A4:D4 which SERIAL NO's i'm aiming for). I've been looking for something that can find the point in column A (MONTH in DataSheet) where the number increases by 1 and the return the SERIAL NO from the cell just before the number increases. But even if I had figured that out, I see a problem for December, since there is nothing but a empty cell after the last entry

Here's both sheets and the workbook.

January	February	March	April	May	June	July	August	September	October	November	December
106	110	115	117

MONTH	DATE	TEXT	SERIAL NO.
1	27-Jan	TEXT	101
1	27-Jan	TEXT	102
1	28-Jan	TEXT	103
1	31-Jan	TEXT	104
1	31-Jan	TEXT	105
1	31-Jan	TEXT	106
2	24-Feb	TEXT	107
2	25-Feb	TEXT	108
2	28-Feb	TEXT	109
2	28-Feb	TEXT	110
3	29-Mar	TEXT	111
3	31-Mar	TEXT	112
3	31-Mar	TEXT	113
3	31-Mar	TEXT	114
3	31-Mar	TEXT	115
4	28-Apr	TEXT	116
4	29-Apr	TEXT	117
5	30-May	TEXT	118
5	30-May	TEXT	119
5	31-May	TEXT	120
6	29-Jun	TEXT	121
6	29-Jun	TEXT	122
6	30-Jun	TEXT	123
6	30-Jun	TEXT	124
7	10-Jul	TEXT	125
8	18-Aug	TEXT	126
8	19-Aug	TEXT	127
9	25-Sep	TEXT	128
9	25-Sep	TEXT	129
9	27-Sep	TEXT	130
9	27-Sep	TEXT	131
10	17-Oct	TEXT	132
11	28-Nov	TEXT	133
11	29-Nov	TEXT	134
11	30-Nov	TEXT	135
11	30-Nov	TEXT	136
12	29-Dec	TEXT	137
12	29-Dec	TEXT	138

TESTWorkbook.xlsx
	A
2
Months

 

[Fluff]

You would need to have actual dates in A1:L1 for the 1st of each month & then use

=AGGREGATE(14,6,Tabel10[[SERIAL NO.]:[SERIAL NO.]]/(Tabel10[[DATE]:[DATE]]>=A$1)/(Tabel10[[DATE]:[DATE]]<=EOMONTH(A$1,0)),1)

 

[Want2BExcel]

You would need to have actual dates in A1:L1 for the 1st of each month & then use

=AGGREGATE(14,6,Tabel10[[SERIAL NO.]:[SERIAL NO.]]/(Tabel10[[DATE]:[DATE]]>=A$1)/(Tabel10[[DATE]:[DATE]]<=EOMONTH(A$1,0)),1)

I'll Try it out when I get there. It has been another great learning experience. Thank You!!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Want2BExcel]

Ok, for one years worth of data on the sheet try

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	MONTH	DATE	TEXT	SERIAL NO.		January	February	March	April	May	June	July	August	September	October	November	December
2	1	27/01/2021	TEXT	101		106	110	115	117	120	124	125	127	131	132	136	138
3	1	27/01/2021	TEXT	102
4	1	28/01/2021	TEXT	103
5	1	31/01/2021	TEXT	104
6	1	31/01/2021	TEXT	105
7	1	31/01/2021	TEXT	106
8	2	24/02/2021	TEXT	107
9	2	25/02/2021	TEXT	108
10	2	28/02/2021	TEXT	109
11	2	28/02/2021	TEXT	110
12	3	29/03/2021	TEXT	111
13	3	31/03/2021	TEXT	112
14	3	31/03/2021	TEXT	113
15	3	31/03/2021	TEXT	114
16	3	31/03/2021	TEXT	115
17	4	28/04/2021	TEXT	116
18	4	29/04/2021	TEXT	117
19	5	30/05/2021	TEXT	118
20	5	30/05/2021	TEXT	119
21	5	31/05/2021	TEXT	120
22	6	29/06/2021	TEXT	121
23	6	29/06/2021	TEXT	122
24	6	30/06/2021	TEXT	123
25	6	30/06/2021	TEXT	124
26	7	10/07/2021	TEXT	125
27	8	18/08/2021	TEXT	126
28	8	19/08/2021	TEXT	127
29	9	25/09/2021	TEXT	128
30	9	25/09/2021	TEXT	129
31	9	27/09/2021	TEXT	130
32	9	27/09/2021	TEXT	131
33	10	17/10/2021	TEXT	132
34	11	28/11/2021	TEXT	133
35	11	29/11/2021	TEXT	134
36	11	30/11/2021	TEXT	135
37	11	30/11/2021	TEXT	136
38	12	29/12/2021	TEXT	137
39	12	29/12/2021	TEXT	138
Data

Cell Formulas
Range	Formula
F2:Q2	F2	=AGGREGATE(14,6,$D$2:$D$39/($A$2:$A$39=COLUMNS($F2:F2)),1)

Hi again Fluff...I guess I didn't think that a few columns in the Months Sheet would change everything (The downside of not fully comprehend what is going on after all)
I tried transferring the formula to my project, but the something happened because I have 3 columns before the column where I enter the formula. So now I get April result in January etc. because COLUMNS($D$2:D$2) is {4}
What can I do?

Text	Text2	Text3	January	February	March	April	May	June	July	August	September	October	November	December
			117	120	124	125	127	131	132	136	138	138	138	138
			106	110	115	117	120	124

 

[Fluff]

COLUMNS($D$2:D$2) is {4}

This will return 1 not 4

 

[Want2BExcel]

This will return 1 not 4

Changing D to A returns 1 again

 

[Want2BExcel]

This will return 1 not 4

You're absolutely right. Missed the S in COLUMNS

 

[Want2BExcel]

This will return 1 not 4

Even though, it seems to only work right when "Serial No" is in a sequence. If the values of "Serial No" is random I get this weird results. Only July show the right value...

TESTWorkbook.xlsx
	A
2
Months

 

[Fluff]

That's because I wrote the formula for the data you posted.

 

[Want2BExcel]

That's because I wrote the formula for the data you posted.

Oh...yes, and it worked. I thought I could transfer it to my project. I should have been more clear...sry! Sometimes I think I have made it clear enough....but it easy to think that when I know what I want.
I should have made it clear that the formulas criteria have to be based on month. More precise the point where month changes in column A, from 1 to 2, 2 to 3 etc. When that is found I need it to get the value in "Serial No." column just before it changes to a new month (Well it is actually an amount, but I didn't think that was important)