Odd date range / price match query

Results 1 to 10 of 10

Thread: Odd date range / price match query

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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. #2
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Dec 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Odd date range / price match query

    Quote Originally Posted by Tetra201 View Post
    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. #4
    New Member
    Join Date
    Dec 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Dec 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    New Member
    Join Date
    Dec 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Odd date range / price match query

    Quote Originally Posted by Tetra201 View Post
    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. #8
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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."
    Last edited by Tetra201; Dec 6th, 2017 at 04:36 PM.

  9. #9
    New Member
    Join Date
    Dec 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Odd date range / price match query

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

  10. #10
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Odd date range / price match query

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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com