Formula to capture the product value on the closest previous date

RahulF

New Member
Joined
Nov 7, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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. :)
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,234
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
will this works for you?

Book1
ABCDE
1ProductDateOldNew
2100005/11/2020250225
3100008/11/2020225210
4200001/11/202010501025
5200008/11/20201025975
6
7
8100005/11/2020225
9100006/11/2020225
10100007/11/2020225
11100008/11/2020210
12100009/11/2020210
13
Sheet2
Cell Formulas
RangeFormula
C8:C12C8=LOOKUP(B8,IF(A8=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5)
B9:B12B9=1+B8
Press CTRL+SHIFT+ENTER to enter array formulas.
 

RahulF

New Member
Joined
Nov 7, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
will this works for you?

Book1
ABCDE
1ProductDateOldNew
2100005/11/2020250225
3100008/11/2020225210
4200001/11/202010501025
5200008/11/20201025975
6
7
8100005/11/2020225
9100006/11/2020225
10100007/11/2020225
11100008/11/2020210
12100009/11/2020210
13
Sheet2
Cell Formulas
RangeFormula
C8:C12C8=LOOKUP(B8,IF(A8=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5)
B9:B12B9=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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,483
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHI
1ProductDateOldNewProductDatePrice
210005/11/202025022510004/11/2020#N/A250
310008/11/202022521010005/11/2020225225
420001/11/20201050102510006/11/2020225225
520008/11/2020102597510007/11/2020225225
610008/11/2020210210
710009/11/2020210210
Lookup Price
Cell Formulas
RangeFormula
H2:H7H2=LOOKUP(G2,IF(F2=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5)
I2:I7I2=IFERROR(LOOKUP(G2,IF(F2=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5),VLOOKUP(F2,A:C,3,0))
G3:G7G3=1+G2
 

RahulF

New Member
Joined
Nov 7, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHI
1ProductDateOldNewProductDatePrice
210005/11/202025022510004/11/2020#N/A250
310008/11/202022521010005/11/2020225225
420001/11/20201050102510006/11/2020225225
520008/11/2020102597510007/11/2020225225
610008/11/2020210210
710009/11/2020210210
Lookup Price
Cell Formulas
RangeFormula
H2:H7H2=LOOKUP(G2,IF(F2=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5)
I2:I7I2=IFERROR(LOOKUP(G2,IF(F2=$A$2:$A$5,$B$2:$B$5),$D$2:$D$5),VLOOKUP(F2,A:C,3,0))
G3:G7G3=1+G2
Hi guys. I was able to get the job done. Thanks :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,279
Messages
5,571,288
Members
412,375
Latest member
BRJoeyMelo
Top