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

Kelleyscott

New Member
Joined
May 21, 2020
Messages
6
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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))
 
Solution

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,141
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

Active Member
Joined
Feb 26, 2014
Messages
382
Office Version
  1. 365
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.
 

billszysz

Active Member
Joined
Feb 26, 2014
Messages
382
Office Version
  1. 365

ADVERTISEMENT

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.
 

Kelleyscott

New Member
Joined
May 21, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,228
Office Version
  1. 365
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,675
Members
425,229
Latest member
Rashid mahmood

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
Top