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]

Will you only ever have one years worth of data?

 

[Want2BExcel]

Will you only ever have one years worth of data?

Well yes and no.... For is now, Yes. I use a datasheet specific to 2021, 2022 etc. But I'm working on just using one DataSheet (then it's No to your question), since I have to create a new query for each year. NOT very user-friendly BUT the Months sheet will ALWAYS be a sheet for every year
Its a working progress (as I learn more and more, I tend to widen my goals ) I may change something.

 

[Fluff]

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)

 

[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)

Yes it works Had to edit it a little since it on two different sheets. But the cell reference is always the same A$2 to L$2, so it ends up to this:

=AGGREGATE(14,6,Tabel10[SERIAL NO.]/(Tabel10[MONTH]=COLUMNS(A$2)),1)

and so fourt with B$2 etc.
But it's just what I imagined, Thank you!
...And when I come to the point where I just use one DataSheet covering multiple years. What then?

 

[Fluff]

The formula needs to be

=AGGREGATE(14,6,Tabel10[SERIAL NO.]/(Tabel10[MONTH]=COLUMNS($A$2:A$2)),1)

and then drag right otherwise you will just get Decembers number each time.

 

[Want2BExcel]

The formula needs to be

=AGGREGATE(14,6,Tabel10[SERIAL NO.]/(Tabel10[MONTH]=COLUMNS($A$2:A$2)),1)

and then drag right otherwise you will just get Decembers number each time.

Ok, I see your point. But I have never figured out how to "absolute" reference this part: Tabel10[SERIAL NO.]/(Tabel10[MONTH]. I know $A$2 makes A2 an "absolute. But when I drag a formula with these table references the table references changes
=AGGREGATE(14,6,Tabel10[MONTH]/(Tabel10[DATE]=COLUMNS($A$2:A$2)),1)
=AGGREGATE(14,6,Tabel10[DATE]/(Tabel10[TEXT]=COLUMNS($A$2:A$2)),1)
=AGGREGATE(14,6,Tabel10[TEXT]/(Tabel10[SERIAL NO.]=COLUMNS($A$2:A$2)),1)
Creating #NUM! errors

 

[Want2BExcel]

The formula needs to be

=AGGREGATE(14,6,Tabel10[SERIAL NO.]/(Tabel10[MONTH]=COLUMNS($A$2:A$2)),1)

and then drag right otherwise you will just get Decembers number each time.

I figured it out. Guess I just gave up a few years ago and never though of searching for an answar now I know what to look for
=AGGREGATE(14,6,Tabel10[[SERIAL NO.]:[SERIAL NO.]]/(Tabel10[[MONTH]:[MONTH]]=COLUMNS($A$2:A$2)),1)

 

[Fluff]

Glad you sorted it.

 

[Want2BExcel]

Glad you sorted it.

Yeah me to...I really have been fighting with that for a loooong time. I just didn't know what to search for back then...guess I've learned a little, LOL!

...And what do I do with this formula when I come to the point where I just use one DataSheet covering multiple years? Is that a whole new question or is it easy edited to fit that task?