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

Kelleyscott

New Member
Joined
May 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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 CodeDate of ChangeProduct NameOld RateNew Rate
5758/10/2019MSV3.193.04
57511/3/2020MSV3.042.87
57531/7/2020MSV2.872.75
11423/8/2019INV2.753.3
1148/10/2019INV3.33.19
11411/3/2020INV3.192.99
1141/4/2020INV2.992.49
11417/11/2020INV2.492.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)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi,

See if this works for you:
Book1
ABCDEFGHIJ
1Funded Date 1/7/2020InputOutput
2Product CodeDate of ChangeProduct NameOld RateNew RateFunded date1-7-2020
35758-10-2019MSV3,193,04Product Code575
457511-3-2020MSV3,042,87
557531-7-2020MSV2,872,75Date of Change11-3-2020
611423-8-2019INV2,753,3New Rate2,87
71148-10-2019INV3,33,19
811411-3-2020INV3,192,99
91141-4-2020INV2,992,49
1011417-11-2020INV2,492,09
Sheet1
Cell Formulas
RangeFormula
J5J5=MAXIFS($B$3:$B$10,$A$3:$A$10,I3,$B$3:$B$10,"<"&I2)
J6J6=FILTER($E$3:$E$10,($A$3:$A$10=I3)*($B$3:$B$10=J5))
 
Upvote 0
Solution
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)
 
Upvote 0
One formula (new function used)
Zeszyt1
FGHIJKLM
1Funded Date2020-07-01
2
3Product CodeDate of ChangeProduct NameOld RateNew RateProduct CodeNew Rate
45752019-10-08MSV (MSV)3,193,045752,87
55752020-03-11MSV (MSV)3,042,871142,49
65752020-07-31MSV (MSV)2,872,75
71142019-08-23INV2,753,3
81142019-10-08INV3,33,19
91142020-03-11INV3,192,99
101142020-04-01INV2,992,49
111142020-11-17INV2,492,09
Arkusz2
Cell Formulas
RangeFormula
L4:M5L4=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.
 
Upvote 0
Ooops, haven't seen you want "Date of Change" as well.....so
Zeszyt1
FGHIJKLMN
1Funded Date2020-07-01
2
3Product CodeDate of ChangeProduct NameOld RateNew RateProduct CodeDate of ChangeNew Rate
45752019-10-08MSV (MSV)3,193,045752020-03-112,87
55752020-03-11MSV (MSV)3,042,871142020-04-012,49
65752020-07-31MSV (MSV)2,872,75
71142019-08-23INV2,753,3
81142019-10-08INV3,33,19
91142020-03-11INV3,192,99
101142020-04-01INV2,992,49
111142020-11-17INV2,492,09
12
Arkusz2
Cell Formulas
RangeFormula
L4:N5L4=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.
 
Upvote 0
Hi,

See if this works for you:
Book1
ABCDEFGHIJ
1Funded Date 1/7/2020InputOutput
2Product CodeDate of ChangeProduct NameOld RateNew RateFunded date1-7-2020
35758-10-2019MSV3,193,04Product Code575
457511-3-2020MSV3,042,87
557531-7-2020MSV2,872,75Date of Change11-3-2020
611423-8-2019INV2,753,3New Rate2,87
71148-10-2019INV3,33,19
811411-3-2020INV3,192,99
91141-4-2020INV2,992,49
1011417-11-2020INV2,492,09
Sheet1
Cell Formulas
RangeFormula
J5J5=MAXIFS($B$3:$B$10,$A$3:$A$10,I3,$B$3:$B$10,"<"&I2)
J6J6=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
 
Upvote 0
If interested, you can get both results with this single formula in cell H7

21 08 11.xlsm
ABCDEFGHI
1Funded Date 1/7/2020Input
2Product CodeDate of ChangeProduct NameOld RateNew RateFunded date01-Jul-20
357508-Oct-19MSV3.193.04Product Code575
457511-Mar-20MSV3.042.87
557531-Jul-20MSV2.872.75Output
611423-Aug-19INV2.753.3Date of ChangeNew Rate
711408-Oct-19INV3.33.1911-Mar-202.87
811411-Mar-20INV3.192.99
911401-Apr-20INV2.992.49
1011417-Nov-20INV2.492.09
Date & New Rate
Cell Formulas
RangeFormula
H7:I7H7=INDEX(SORT(FILTER(B3:E10,(A3:A10=I3)*(B3:B10<I2)),1,-1),1,{1,4})
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,915
Messages
6,181,723
Members
453,064
Latest member
robatthe2A

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