Odd date range / price match query

jamiejohn187

New Member
Joined
Dec 4, 2017
Messages
6
Hi guys,

I need to essentially vlookup product codes on one spreadsheet to see on a particular date what they were sold for.

Here is an odd breakdown of my issue:

Sheet 1
Product
Price
Date Started
Date Finished
XYZABC
19.99
01/10/2017
04/10/2017
XYZABC
25.99
05/10/2017
XYZABC
32.99
11/09/2017
30/09/2017
ABCXYZ1
11.99
01/10/2017
04/10/2017
ABCXYZ1
5.99
05/10/2017
ABCXYZ1
6.99
11/09/2017
30/09/2017
Sheet 2
Product
Sold Date
Price
XYZABC
03/10/2017
XYZABC
06/10/2017
XYZABC
13/09/2017
ABCXYZ1
02/10/2017
ABCXYZ1
20/09/2017
ABCXYZ1
01/11/2017

<tbody>
</tbody>


So on Sheet 2 I need to return the price in column C of the price on Product for a specific date where only the date range is given in sheet 1.. Any help you can provide would be monumental.

Thanks!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:

=MIN(IF((Sheet1!$A$2:$A$7=A2)*(Sheet1!$C$2:$C$7<=B2)*(IFERROR(1/(1/Sheet1!$D$2:$D$7),999999)>=B2),Sheet1!$B$2:$B$7))
 
Upvote 0
See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:

=MIN(IF((Sheet1!$A$2:$A$7=A2)*(Sheet1!$C$2:$C$7<=B2)*(IFERROR(1/(1/Sheet1!$D$2:$D$7),999999)>=B2),Sheet1!$B$2:$B$7))

Haha. This works perfectly on the data I gave you... you amazing person... HOWEVER... it does not work on my actual data.

Here is the formula I have constructed translated from your answer (I have written it a number of times and see no errors - but I could be mistaken)

=MIN(IF(('Price in Cat'!$A$3:$A$9='Sales Data'!F5)*('Price in Cat'!$E$3:$E$9<='Sales Data'!G5)*(IFERROR(1/(1/'Price in Cat'!$F$3:$F$9),999999)>='Sales Data'!G5),'Price in Cat'!$G$3:$G$9))

I have changed out the specific data to match the tabs I need (and am using), i.e. Sales Data for Sheet2 and Price in Cat for Sheet1, additionally to point at the specific columns in relation to your above answer, yet the returned value is always 0. I am also using CTRL + SHIFT+ENTER.

:)
 
Upvote 0
Okay, please disregard my last question. I now know why your formula worked and it will not work in the actual data..

Essentially, between some of the price changes, the price reverts back to an earlier price.. for example:

ProductPRICE_START_DATEEND_DATEPRICE_VALUE
ABCXYZ30/11/201639.99
ABCXYZ04/01/201744.99
ABCXYZ22/03/201705/04/201719.99
ABCXYZ09/06/201721/06/201724.99
ABCXYZ30/06/201739.99
ABCXYZ02/08/201716/08/201719.99
ABCXYZ04/10/201718/10/201719.99

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>


So on April 6th the price would revert from 19.99 back to the 44.99, before dropping again on 9th June to 24.99

Sorry, my original data did not reflect this.

So if the above example is Sheet1 the below data would be Sheet2

ProductSold DatePRICE?
ABCXYZ05/05/2016

<tbody>
</tbody>
 
Upvote 0
So, if the Sold Date does not fall into any fixed date range, you want the price to revert to that of the most recent open-ended range?

If yes, then try

=LOOKUP(2,1/(Sheet1!$A$2:$A$8=A2)/(Sheet1!$B$2:$B$8<=B2)/(IFERROR(1/(1/Sheet1!$C$2:$C$8),999999)>=B2),Sheet1!$D$2:$D$8)

Note 1: This is an array formula; it has to be entered using Ctrl+Shift+Enter, not just Enter.
Note 2: You changed the column order. This formula is for the layout shown in Post # 4.
 
Upvote 0
Yes, if not falling into a fixed date range then to revert to most recent open date! Will give this one a go when I'm at my desk in a bit. Once again Tetra201, thank you very much! Will let you know how this goes!
 
Upvote 0
So, if the Sold Date does not fall into any fixed date range, you want the price to revert to that of the most recent open-ended range?

If yes, then try

=LOOKUP(2,1/(Sheet1!$A$2:$A$8=A2)/(Sheet1!$B$2:$B$8<=B2)/(IFERROR(1/(1/Sheet1!$C$2:$C$8),999999)>=B2),Sheet1!$D$2:$D$8)

Note 1: This is an array formula; it has to be entered using Ctrl+Shift+Enter, not just Enter.
Note 2: You changed the column order. This formula is for the layout shown in Post # 4.

Me again!

So, no.. that does not work, sadly. It doesn't appear to work even on the sample data from the looks of things.
 
Upvote 0
Sadly, "that does not work" is not really helpful for troubleshooting the issue.

What would be of more assistance is something like this: "Using the sample dataset from Post # X, the formula should return $ZZ.ZZ for product EFGHIJ sold on DD/MM/YYYY. Instead, it returns $QQ.QQ."
 
Last edited:
Upvote 0
... Essentially, between some of the price changes, the price reverts back to an earlier price.. for example:

Product
PRICE_START_DATE
END_DATE
PRICE_VALUE
ABCXYZ
30/11/2016
39.99
ABCXYZ
04/01/2017
44.99
ABCXYZ
22/03/2017
05/04/2017
19.99
ABCXYZ
09/06/2017
21/06/2017
24.99
ABCXYZ
30/06/2017
39.99
ABCXYZ
02/08/2017
16/08/2017
19.99
ABCXYZ
04/10/2017
18/10/2017
19.99

<tbody>
</tbody>


So on April 6th the price would revert from 19.99 back to the 44.99, before dropping again on 9th June to 24.99

Sorry, my original data did not reflect this.

So if the above example is Sheet1 the below data would be Sheet2

Product
Sold Date
PRICE?
ABCXYZ
05/05/2016

<tbody>
</tbody>
Using the sample data, the only thing being returned is #N/A
Using the sample dataset from Post # 4, my formula from Post # 5 returns the following prices for product ABCXYZ:

05/12/2016 ... 39.99
05/03/2017 ... 44.99
05/04/2017 ... 19.99
05/05/2017 ... 44.99
15/06/2017 ... 24.99
05/07/2017 ... 39.99
05/08/2017 ... 19.99
05/09/2017 ... 39.99
05/10/2017 ... 19.99

It does return #N/A for 05/05/2016 as the earliest available price is dated 30/11/2016.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top