Formula to capture the product value on the closest previous date

[RahulF]

Hi. Hope you are safe and well. I was trying to figure this out the whole day but couldn't and if it's not too much trouble can someone help me with this.

I have a multitude of product (part) numbers and the dates on which the selling price of such products (parts) were changed.

Example:
Product Number (Code) Date Old Price New Price
1000 05/11/2020 $250.00 $225.00
1000 08/11/2020 $225.00 $210.00
2000 01/11/2020 $1050.00 $1025.00
2000 08/11/2020 $1025.00 $975.00

If you consider the product code 1000, on 05th November its' price has been reduced to $225.00 and reduced to $210.00 again on 08th November. The price for this product on 07th November is $225.00 as the subsequent price reduction has occurred on 08th.

Is there a formula that I can use to capture the price for a particular product code if the date is not available in the Price Changes Excel Sheet. For instance if I want to capture price (which I can tell is $225.00 by simply looking at the table) for product code1000 on 07th November, is there a formula which I can use?

Thanks in advance.

 

[AlanY]

will this works for you?

Book1
	A	B	C	D	E
1	Product	Date	Old	New
2	1000	05/11/2020	250	225
3	1000	08/11/2020	225	210
4	2000	01/11/2020	1050	1025
5	2000	08/11/2020	1025	975
6
7
8	1000	05/11/2020	225
9	1000	06/11/2020	225
10	1000	07/11/2020	225
11	1000	08/11/2020	210
12	1000	09/11/2020	210
13
Sheet2

Cell Formulas
Range	Formula
C8:C12	C8	=LOOKUP(B8,IF(A8=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5)
B9:B12	B9	=1+B8
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RahulF]

will this works for you?

Book1
	A	B	C	D	E
1	Product	Date	Old	New
2	1000	05/11/2020	250	225
3	1000	08/11/2020	225	210
4	2000	01/11/2020	1050	1025
5	2000	08/11/2020	1025	975
6
7
8	1000	05/11/2020	225
9	1000	06/11/2020	225
10	1000	07/11/2020	225
11	1000	08/11/2020	210
12	1000	09/11/2020	210
13
Sheet2

Cell Formulas
Range	Formula
C8:C12	C8	=LOOKUP(B8,IF(A8=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5)
B9:B12	B9	=1+B8
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi Alan. Will check this. I think this'll do.

Thanks a bunch

Really appreciate it.

 

[AlanY]

you're welcome

 

[Peter_SSs]

If you might want to look up the price of a product before the first change in the table the formula would need amendment - see H2 below.
Alan may have another amendment but one would be as shown in column I.
All suggestions do assume that for a particular product the dates increase down the worksheet.

20 11 08.xlsm
	A	B	C	D	E	F	G	H	I
1	Product	Date	Old	New		Product	Date	Price
2	1000	5/11/2020	250	225		1000	4/11/2020	#N/A	250
3	1000	8/11/2020	225	210		1000	5/11/2020	225	225
4	2000	1/11/2020	1050	1025		1000	6/11/2020	225	225
5	2000	8/11/2020	1025	975		1000	7/11/2020	225	225
6						1000	8/11/2020	210	210
7						1000	9/11/2020	210	210
Lookup Price

Cell Formulas
Range	Formula
H2:H7	H2	=LOOKUP(G2,IF(F2=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5)
I2:I7	I2	=IFERROR(LOOKUP(G2,IF(F2=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5),VLOOKUP(F2,A:C,3,0))
G3:G7	G3	=1+G2

 

[RahulF]

If you might want to look up the price of a product before the first change in the table the formula would need amendment - see H2 below.
Alan may have another amendment but one would be as shown in column I.
All suggestions do assume that for a particular product the dates increase down the worksheet.

20 11 08.xlsm
	A	B	C	D	E	F	G	H	I
1	Product	Date	Old	New		Product	Date	Price
2	1000	5/11/2020	250	225		1000	4/11/2020	#N/A	250
3	1000	8/11/2020	225	210		1000	5/11/2020	225	225
4	2000	1/11/2020	1050	1025		1000	6/11/2020	225	225
5	2000	8/11/2020	1025	975		1000	7/11/2020	225	225
6						1000	8/11/2020	210	210
7						1000	9/11/2020	210	210
Lookup Price

Cell Formulas
Range	Formula
H2:H7	H2	=LOOKUP(G2,IF(F2=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5)
I2:I7	I2	=IFERROR(LOOKUP(G2,IF(F2=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5),VLOOKUP(F2,A:C,3,0))
G3:G7	G3	=1+G2

Hi guys. I was able to get the job done. Thanks

 

[Peter_SSs]

Hi guys. I was able to get the job done. Thanks

Glad you got a successful result. Thanks for the follow-up.