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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
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,926
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,122,589
Messages
5,597,053
Members
414,118
Latest member
moversnpackers

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