Match a value and then return the date that is less than a given date

[Kelleyscott]

I am wanting to create a formula that will provide me with the closest date that was prior to the funded date - whilst matching it specifically to that product code only.

Funded Date 1/7/2020

Product Code	Date of Change	Product Name	Old Rate	New Rate
575	8/10/2019	MSV	3.19	3.04
575	11/3/2020	MSV	3.04	2.87
575	31/7/2020	MSV	2.87	2.75
114	23/8/2019	INV	2.75	3.3
114	8/10/2019	INV	3.3	3.19
114	11/3/2020	INV	3.19	2.99
114	1/4/2020	INV	2.99	2.49
114	17/11/2020	INV	2.49	2.09

EG, For product code 575, when was the last rate change (date) that is prior to the funded date. For this example, the answer is 11/3/2020. (because the 31/7 is after the funded date 1/7/2020).
I also want the same type of formula to then also give me what the 'New rate" was on that date (11/3/20)

Similarly, if I want the same information but for product code 114, what is the when was the last rate change (date) that is prior to the funded date. For this example, the answer is 1/4/2020. (because 17/11 is after the funded date 1/7/2020)
I also want the same type of formula to then also give me what the 'New rate" was on that date (1/4/20)

 

[jorismoerings]

Hi,

See if this works for you:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Funded Date 1/7/2020								Input	Output
2	Product Code	Date of Change	Product Name	Old Rate	New Rate			Funded date	1-7-2020
3	575	8-10-2019	MSV	3,19	3,04			Product Code	575
4	575	11-3-2020	MSV	3,04	2,87
5	575	31-7-2020	MSV	2,87	2,75			Date of Change		11-3-2020
6	114	23-8-2019	INV	2,75	3,3			New Rate		2,87
7	114	8-10-2019	INV	3,3	3,19
8	114	11-3-2020	INV	3,19	2,99
9	114	1-4-2020	INV	2,99	2,49
10	114	17-11-2020	INV	2,49	2,09
Sheet1

Cell Formulas
Range	Formula
J5	J5	=MAXIFS($B$3:$B$10,$A$3:$A$10,I3,$B$3:$B$10,"<"&I2)
J6	J6	=FILTER($E$3:$E$10,($A$3:$A$10=I3)*($B$3:$B$10=J5))

 

[jkpieterse]

Suppose product code is in A10 and funded date is in A11 and your table starts in A1:
=MAXIFS($B$2:$B$9,$A$2:$A$9,A10,$B$2:$B$9,"<"&A11)

 

[billszysz]

One formula (new function used)

Zeszyt1
	F	G	H	I	J	K	L	M
1	Funded Date	2020-07-01
2
3	Product Code	Date of Change	Product Name	Old Rate	New Rate		Product Code	New Rate
4	575	2019-10-08	MSV (MSV)	3,19	3,04		575	2,87
5	575	2020-03-11	MSV (MSV)	3,04	2,87		114	2,49
6	575	2020-07-31	MSV (MSV)	2,87	2,75
7	114	2019-08-23	INV	2,75	3,3
8	114	2019-10-08	INV	3,3	3,19
9	114	2020-03-11	INV	3,19	2,99
10	114	2020-04-01	INV	2,99	2,49
11	114	2020-11-17	INV	2,49	2,09
Arkusz2

Cell Formulas
Range	Formula
L4:M5	L4	=LET(u, UNIQUE(F4:F11), nr, MAP(u,LAMBDA(x,INDEX(SORT(FILTER(F4:J11,(F4:F11=x)*(G4:G11<=G1)),2,-1),1,5))), CHOOSE({1,2},u,nr))
Dynamic array formulas.

 

[billszysz]

Ooops, haven't seen you want "Date of Change" as well.....so

Zeszyt1
	F	G	H	I	J	K	L	M	N
1	Funded Date	2020-07-01
2
3	Product Code	Date of Change	Product Name	Old Rate	New Rate		Product Code	Date of Change	New Rate
4	575	2019-10-08	MSV (MSV)	3,19	3,04		575	2020-03-11	2,87
5	575	2020-03-11	MSV (MSV)	3,04	2,87		114	2020-04-01	2,49
6	575	2020-07-31	MSV (MSV)	2,87	2,75
7	114	2019-08-23	INV	2,75	3,3
8	114	2019-10-08	INV	3,3	3,19
9	114	2020-03-11	INV	3,19	2,99
10	114	2020-04-01	INV	2,99	2,49
11	114	2020-11-17	INV	2,49	2,09
12
Arkusz2

Cell Formulas
Range	Formula
L4:N5	L4	=LET(u, UNIQUE(F4:F11), date, MAP(u,LAMBDA(x,INDEX(SORT(FILTER(F4:J11,(F4:F11=x)*(G4:G11<=G1)),2,-1),1,2))), nr, MAP(u,LAMBDA(x,INDEX(SORT(FILTER(F4:J11,(F4:F11=x)*(G4:G11<=G1)),2,-1),1,5))), CHOOSE({1,2,3},u,date,nr))
Dynamic array formulas.

 

[Kelleyscott]

Hi,

See if this works for you:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Funded Date 1/7/2020								Input	Output
2	Product Code	Date of Change	Product Name	Old Rate	New Rate			Funded date	1-7-2020
3	575	8-10-2019	MSV	3,19	3,04			Product Code	575
4	575	11-3-2020	MSV	3,04	2,87
5	575	31-7-2020	MSV	2,87	2,75			Date of Change		11-3-2020
6	114	23-8-2019	INV	2,75	3,3			New Rate		2,87
7	114	8-10-2019	INV	3,3	3,19
8	114	11-3-2020	INV	3,19	2,99
9	114	1-4-2020	INV	2,99	2,49
10	114	17-11-2020	INV	2,49	2,09
Sheet1

Cell Formulas
Range	Formula
J5	J5	=MAXIFS($B$3:$B$10,$A$3:$A$10,I3,$B$3:$B$10,"<"&I2)
J6	J6	=FILTER($E$3:$E$10,($A$3:$A$10=I3)*($B$3:$B$10=J5))

thank you so much for your assistance. this has worked

 

[Peter_SSs]

If interested, you can get both results with this single formula in cell H7

21 08 11.xlsm
	A	B	C	D	E	F	G	H	I
1	Funded Date 1/7/2020							Input
2	Product Code	Date of Change	Product Name	Old Rate	New Rate			Funded date	01-Jul-20
3	575	08-Oct-19	MSV	3.19	3.04			Product Code	575
4	575	11-Mar-20	MSV	3.04	2.87
5	575	31-Jul-20	MSV	2.87	2.75			Output
6	114	23-Aug-19	INV	2.75	3.3			Date of Change	New Rate
7	114	08-Oct-19	INV	3.3	3.19			11-Mar-20	2.87
8	114	11-Mar-20	INV	3.19	2.99
9	114	01-Apr-20	INV	2.99	2.49
10	114	17-Nov-20	INV	2.49	2.09
Date & New Rate

Cell Formulas
Range	Formula
H7:I7	H7	=INDEX(SORT(FILTER(B3:E10,(A3:A10=I3)*(B3:B10<I2)),1,-1),1,{1,4})
Dynamic array formulas.