Formula to find value, in maximum month, in every year

[adityoreksoprawiroAHM]

Hi,
I want to use formula Formula to find value, in maximum month, in every year.

Kurs Transaksi USD .xlsx
	F
8
Kurs Transaksi USD

how to put formula in kurs jual or kurs beli?

 

[adityoreksoprawiroAHM]

Hi,
I want to use formula Formula to find value, in maximum month, in every year.

Kurs Transaksi USD .xlsx
	F
8
Kurs Transaksi USD

how to put formula in kurs jual or kurs beli?

i put wrong mini sheet, please refer to this mini sheet

Kurs Transaksi USD .xlsx
	A	B	C	D	E	F	G	H	I	J
3	Kurs Transaksi USD
4
5	NO	Nilai	Kurs Jual	Kurs Beli	Date			Month	Kurs Jual	Kurs Beli
6	1	1	15336	15183,7	06/09/2023			Sep-23	15.336,3	15.183,7
7	2	1	15323	15170,76	05/09/2023			Aug-23	15.329,3	15.176,7
8	3	1	15328	15175,74	04/09/2023			Nov-22	15.794,6	15.637,4
9	9	1	15329	15176,74	25/08/2023
10	10	1	15396	15242,41	24/08/2023
11	11	1	15403	15249,37	23/08/2023
12	12	1	15406	15252,35	22/08/2023
13	13	1	15385	15231,46	21/08/2023
14	188	1	15795	15637,42	23/11/2022
15	189	1	15786	15628,47	22/11/2022
16	190	1	15770	15613,54	21/11/2022
17	191	1	15765	15608,57	18/11/2022
18	192	1	15688	15531,95	17/11/2022
Kurs Transaksi USD

Cell Formulas
Range	Formula
I6:J6	I6	=C6
I7:J7	I7	=C9
I8:J8	I8	=C14

 

[kevin9999]

Possibly this:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3	Kurs Transaksi USD
4
5	NO	Nilai	Kurs Jual	Kurs Beli	Date			Month	Kurs Jual	Kurs Beli
6	1	1	15336.3	15183.7	6/09/2023			Sep-23	15336.3	15183.7
7	2	1	15323.24	15170.76	5/09/2023			Aug-23	15405.65	15252.35
8	3	1	15328.26	15175.74	4/09/2023			Nov-22	15794.58	15637.42
9	9	1	15329.26	15176.74	25/08/2023
10	10	1	15395.59	15242.41	24/08/2023
11	11	1	15402.63	15249.37	23/08/2023
12	12	1	15405.65	15252.35	22/08/2023
13	13	1	15384.54	15231.46	21/08/2023
14	188	1	15794.58	15637.42	23/11/2022
15	189	1	15785.53	15628.47	22/11/2022
16	190	1	15770.46	15613.54	21/11/2022
17	191	1	15765.43	15608.57	18/11/2022
18	192	1	15688.05	15531.95	17/11/2022
Sheet1

Cell Formulas
Range	Formula
I6:I8	I6	=AGGREGATE(14,6,$C$6:$C$18/(($E$6:$E$18>=$H6)*($E$6:$E$18<=EOMONTH($H6,0))),1)
J6:J8	J6	=AGGREGATE(14,6,$D$6:$D$18/(($E$6:$E$18>=$H6)*($E$6:$E$18<=EOMONTH($H6,0))),1)

 

[Tetra201]

See if the following formula works for you:

=INDEX(C$6:C$18,MATCH(AGGREGATE(14,3,(TEXT($H6,"yyyymm")=TEXT($E$6:$E$18,"yyyymm"))*$E$6:$E$18,1),$E$6:$E$18,0))

 

[adityoreksoprawiroAHM]

i put wrong mini sheet, please refer to this mini sheet

Kurs Transaksi USD .xlsx
	A	B	C	D	E	F	G	H	I	J
3	Kurs Transaksi USD
4
5	NO	Nilai	Kurs Jual	Kurs Beli	Date			Month	Kurs Jual	Kurs Beli
6	1	1	15336	15183,7	06/09/2023			Sep-23	15.336,3	15.183,7
7	2	1	15323	15170,76	05/09/2023			Aug-23	15.329,3	15.176,7
8	3	1	15328	15175,74	04/09/2023			Nov-22	15.794,6	15.637,4
9	9	1	15329	15176,74	25/08/2023
10	10	1	15396	15242,41	24/08/2023
11	11	1	15403	15249,37	23/08/2023
12	12	1	15406	15252,35	22/08/2023
13	13	1	15385	15231,46	21/08/2023
14	188	1	15795	15637,42	23/11/2022
15	189	1	15786	15628,47	22/11/2022
16	190	1	15770	15613,54	21/11/2022
17	191	1	15765	15608,57	18/11/2022
18	192	1	15688	15531,95	17/11/2022
Kurs Transaksi USD

Cell Formulas
Range	Formula
I6:J6	I6	=C6
I7:J7	I7	=C9
I8:J8	I8	=C14

thank you kevin, it works, perfect!

 

[adityoreksoprawiroAHM]

See if the following formula works for you:

=INDEX(C$6:C$18,MATCH(AGGREGATE(14,3,(TEXT($H6,"yyyymm")=TEXT($E$6:$E$18,"yyyymm"))*$E$6:$E$18,1),$E$6:$E$18,0))

Thank you Tetra, the code much simpler