Odd date range / price match query

# Thread: Odd date range / price match query

1. ## Odd date range / price match query

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

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!

2. ## Re: Odd date range / price match query

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))

3. ## Re: Odd date range / price match query

Originally Posted by Tetra201
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.

4. ## Re: Odd date range / price match query

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:

 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

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

5. ## Re: Odd date range / price match query

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.

6. ## Re: Odd date range / price match query

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!

7. ## Re: Odd date range / price match query

Originally Posted by Tetra201
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.

8. ## Re: Odd date range / price match query

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."

9. ## Re: Odd date range / price match query

Using the sample data, the only thing being returned is #N/A

10. ## Re: Odd date range / price match query

Originally Posted by jamiejohn187
... 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

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
Originally Posted by jamiejohn187
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•