+ Reply to Thread
Results 1 to 21 of 21

Lookup Previous Price In Price List

  1. #1
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    44

    Lookup Previous Price In Price List

    Hi,

    I have got the following scenario. The price list is updated the way new records with the date of update from a supplier are added to it. Each updated record (line) shows further details on Supplier, P/N, Site and MOQ (Minimum Order Quantity). Old records stay in the price list and are never removed. The records may not be sorted.
    I need to look up for each record (line) in the price list the previously valid price (in order to be able to compare it, calculate inflation, etc.) which corresponds to the same Site, Supplier, P/N and same or the closest MOQ.

    Many thanks for help.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    44

    Re: Lookup Previous Price In Price List

    Anyone has an idea?

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup Previous Price In Price List

    You can use this array formula in G2:

    =IFERROR(INDEX(Price_List[Price],MATCH(1,(Price_List[Date]=MAXIFS(Price_List[Date],Price_List[Site],B2,Price_List[Supplier],C2,Price_List[P/N],D2,Price_List[MOQ],E2,Price_List[Date],"<"&A2))*(Price_List[Site]=B2)*(Price_List[Supplier]=C2)*(Price_List[P/N]=D2)*(Price_List[MOQ]=E2),0)),"-")

    As you are using XL365, you do not need to use CSE to confirm the formula, and it should automatically copy itself down.

    Hope this helps.

    Pete

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup Previous Price In Price List

    Note that you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

    Hope this helps.

    Pete

  5. #5
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup Previous Price In Price List

    Please try

    =IFERROR(LOOKUP(2,1/([@MOQ]=(Table1[[#All],[Date]]<[@Date])*(Table1[[#All],[Site]]=[@Site])*(Table1[[#All],[Supplier]]=[@Supplier])*(Table1[[#All],[P/N]]=[@[P/N]])*Table1[[#All],[MOQ]]),Table1[[#All],[Price]]),"-")

    Note : I can't find the nearest value of MOQ, and I don't know did my formula is suit you.

    Regards.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    44

    Re: Lookup Previous Price In Price List

    Hi menem,
    Thanks a lot for your solution. I noticed it does not give correct result if there are 3 different dates as the formula picks the oldest one. Can you fix it?

    I myself tried to find a solution by using INDEX & MATCH but got stuck.
    =INDEX([Price];MATCH(1;(MAX(([Date]<[@Date])*[Date];[Date]))*([@Site]=[Site])*([@Supplier]=[Supplier])*([@[P/N]]=[P/N])*([@MOQ]=[MOQ]);0))
    There is a problem with (MAX(([Date]<[@Date])*[Date];[Date])) which I am not able to modify from the formula MAX(([Date]<[@Date])*[Date]), which returns the previous date, to be one of the criteria in MATCH.
    Plus, my function takes equal MOQ for now.

    I wonder if someone could also fix my function.

    Thank you
    Last edited by tomas777; 06-22-2022 at 04:19 AM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Lookup Previous Price In Price List

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    44

    Re: Lookup Previous Price In Price List

    Hi Ali,

    I am a newbie on this site ans still earning. My 46 rows were closer to the requested 10-20 rows, though. I did not know it should be so strict. I just wanted to avoid those thousands of them.

    Have a great day

    Tomas

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Lookup Previous Price In Price List

    I want to see a version with your latest attempt at using the formula, please, so that I or someone else can troubleshoot it.

  10. #10
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    44

    Re: Lookup Previous Price In Price List

    Hi Pete,
    Thanks a lot!!! Your formula works! Could you also modify it a little further to also return the price of THE CLOSEST MOQ from the previous price record provided the exact MOQ is not in this previous price record?
    Thanks!
    Attached Files Attached Files
    Last edited by tomas777; 06-22-2022 at 06:19 AM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Lookup Previous Price In Price List

    I take it you aren't interested in my help?

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Lookup Previous Price In Price List

    I don't know what you mean by "the CLOSEST MOQ". Please explain further.

    If it is not that critical to the calculation, then perhaps you can omit that criterion.

    Pete

  13. #13
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup Previous Price In Price List

    Please try this array formula

    =IFERROR(INDEX(Table1[[#All],[Price]],MATCH(MAX(1,AGGREGATE(14,6,(Table1[[#All],[Date]]<[@Date])*(Table1[[#All],[Site]]=[@Site])*(Table1[[#All],[Supplier]]=[@Supplier])*(Table1[[#All],[P/N]]=[@[P/N]])*(Table1[[#All],[MOQ]]=[@MOQ])*Table1[[#All],[Date]],1)),
    (Table1[[#All],[Date]]<[@Date])*(Table1[[#All],[Site]]=[@Site])*(Table1[[#All],[Supplier]]=[@Supplier])*(Table1[[#All],[P/N]]=[@[P/N]])*(Table1[[#All],[MOQ]]=[@MOQ])*Table1[[#All],[Date]],0)),"-")

    Regards.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    44

    Re: Lookup Previous Price In Price List

    Hi Pete,

    please see the attached file with comments showing the desired calculation results.
    Attached Files Attached Files
    Last edited by AliGW; 06-22-2022 at 06:19 AM. Reason: PLEASE don't quote unnecessarily!

  15. #15
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    44

    Re: Lookup Previous Price In Price List

    Hi Menem,

    The only thing which does not work as expected is that if the previous price record has a different MOQ, the formula returns the price from the second oldest record instead. Thank you
    Last edited by AliGW; 06-22-2022 at 06:19 AM. Reason: PLEASE don't quote unnecessarily!

  16. #16
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    44

    Re: Lookup Previous Price In Price List

    Quote Originally Posted by AliGW View Post
    I take it you aren't interested in my help?
    Hi Ali,

    Not at all. Let me apologise if I gave you this impression. I was not meant like this. I am just doing my regular job on parallel and cannot reply quickly. I would still appreciate if you fix & finalize my formula in the attached file. Thank you
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup Previous Price In Price List

    Please try an array formula (follow with Ctrl-shift-enter)

    =IF(AGGREGATE(14,6,
    ( ([Date]<[@Date])*([Site]=[@Site])*([Supplier]=[@Supplier])*([P/N]=[@[P/N]]) )*
    ( MAX([MOQ])-ABS([MOQ]-[@MOQ]) ) * ([Date]), 1)>0,
    INDEX([Price],
    MATCH(AGGREGATE(14,6,
    ( ([Date]<[@Date])*([Site]=[@Site])*([Supplier]=[@Supplier])*([P/N]=[@[P/N]]) )*
    ( MAX([MOQ])-ABS([MOQ]-[@MOQ]) ) * ([Date]), 1),
    ( ([Date]<[@Date])*([Site]=[@Site])*([Supplier]=[@Supplier])*([P/N]=[@[P/N]]) )*
    ( MAX([MOQ])-ABS([MOQ]-[@MOQ]) ) * ([Date]),0) ),"-")

    Regards.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    44

    Re: Lookup Previous Price In Price List

    menem,

    you have solved it. Everything works as it should. Thanks a lot!

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Lookup Previous Price In Price List

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  20. #20
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Lookup Previous Price In Price List

    May I ask, which is more important (or more priority) between
    date and MOQ.

    For sample

    Date 15 moq 10

    lookup in

    Date 10 moq 12

    Date 5 moq 11

    which data should be selected.

    Regards.

    I think my formula might be needed to be changed.

  21. #21
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Lookup Previous Price In Price List

    In your post#16, the attached file shows for ROW 26 the answer should be 16.5 from F46. Why shouldn't it be 20 from F30?
    Also, Row 27, you say it should be 12 from F27. It's already in Row 27. Why shouldn't it be 11 from F43?

    If my assumptions are correct, then try this in cell G2:

    =LET(a,FILTER(E3:F$46,(B3:B$46=[@Site])*(C3:C$46=[@Supplier])*(D3:D$46=[@[P/N]]),""),
    IFERROR(XLOOKUP(0,ABS([@MOQ]-INDEX(a,,1)),INDEX(a,,2),"-",1),"-"))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 04-25-2020, 09:24 AM
  2. Replies: 11
    Last Post: 08-06-2018, 05:39 AM
  3. Replies: 10
    Last Post: 08-06-2018, 05:33 AM
  4. Replies: 3
    Last Post: 09-12-2015, 10:42 AM
  5. Replies: 19
    Last Post: 10-20-2014, 03:57 PM
  6. [SOLVED] Lookup Code and return Price from Price Band
    By PaulHAG in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-16-2014, 11:06 AM
  7. Replies: 4
    Last Post: 08-15-2012, 09:49 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1