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

 

[th081]

That was a typo on my part, the dates will be in ascending order with each product code.

 

[Fluff]

Thanks for letting us know.