Pick up applicable rate

[th081]

Hi All,

I have two sheets in my spreadsheet, one contains dates, codes and product rates as below:

Date	Product Code	Price
01/04/2011​	BADE	23
26/07/2011​	BADE	23.7
01/04/2012​	BADE	24
01/04/2013​	BADE	24
01/04/2014​	BADE	28
01/04/2015​	BADE	29
01/04/2016​	BADE	27
01/04/2017​	BADE	30
01/04/2018​	BADE	33
01/04/2019​	BADE	35
01/04/2020​	BADE	36
01/10/2021​	BADE	36.8
01/04/2021​	BADE	36.9
01/04/2009​	CDGE	15
01/04/2021​	CDGE	15.1
01/04/2015​	EYUT	120
01/04/2016​	EYUT	125
01/04/2017​	EYUT	130
01/04/2018​	EYUT	131
01/04/2019​	EYUT	130
01/09/2020​	EYUT	135
01/04/2021​	EYUT	142

In a separate sheet i have dates for a whole year i.e 01/01/2011 to 31/12/2011, how do i lookup and get the rate that was applicable on a particular date? A rate is applicable up to the date a new rate goes live so the product BADE would have the rate 23 from 01/04/11 to 25/06/11 of 23 and then from 26/07/11 the rate will be 23.7 until 31/03/12

Regards

TH081

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[th081]

Thanks, I have updated W10 and 365

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Product Code	Price
2	01/04/2011	BADE	23				27/08/2011	Bade	23.7
3	26/07/2011	BADE	23.7				01/03/2020	eyut	130
4	01/04/2012	BADE	24
5	01/04/2013	BADE	24
6	01/04/2014	BADE	28
7	01/04/2015	BADE	29
8	01/04/2016	BADE	27
9	01/04/2017	BADE	30
10	01/04/2018	BADE	33
11	01/04/2019	BADE	35
12	01/04/2020	BADE	36
13	01/10/2021	BADE	36.8
14	01/04/2021	BADE	36.9
15	01/04/2009	CDGE	15
16	01/04/2021	CDGE	15.1
17	01/04/2015	EYUT	120
18	01/04/2016	EYUT	125
19	01/04/2017	EYUT	130
20	01/04/2018	EYUT	131
21	01/04/2019	EYUT	130
22	01/09/2020	EYUT	135
23	01/04/2021	EYUT	142
24
Master

Cell Formulas
Range	Formula
I2:I3	I2	=INDEX(SORT(FILTER($A$2:$C$23,($B$2:$B$23=H2)*($A$2:$A$23<=G2)),1,-1),1,3)

 

[th081]

Thank you, unfortunately the SORT function does not appear to be available to me. It is a Office Office 365 ProPlus version but a corporate version and it says updates are controlled by the administrator so the SORT update has probably not been pushed through yet.

 

[Fluff]

Ok, how about

=INDEX($C$2:$C$23,AGGREGATE(14,6,(ROW($C$2:$C$23)-ROW($C$2)+1)/($B$2:$B$23=H2)/($A$2:$A$23<=G2),1))

 

[th081]

Thank you very much Fluff, that works.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Toadstool]

Ok, how about

=INDEX($C$2:$C$23,AGGREGATE(14,6,(ROW($C$2:$C$23)-ROW($C$2)+1)/($B$2:$B$23=H2)/($A$2:$A$23<=G2),1))

I believe this would need the Date to be ascending (at least within Product Code).
e.g. A BADE entry for 1 November 2021 will retrieve the row 14 for 1 April 2021 value of 36.9 instead of the row 13 for 1 October 2021 value of 36.8.

 

[Fluff]

You're quite right, I hadn't noticed that there was one date out of order.