1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pricing Tier Lookup

Discussion in 'Ask an Excel Question' started by Vernon G, Nov 23, 2018.

  1. Vernon G

    Vernon G New Member

    Messages:
    8
    HI
    First time user, pls help.

    In attached file I need to lookup a stock code(in cell E2) in Column A, and return a price(in cell G2) from Column C) based on quantity (in cell F2) as per quantity break in Column B for each of the stock codes in Column A.

    Your assistance will be greatly appreciated.

    Vernon

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,415

    Attached Files:

    Vernon G likes this.
  3. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    This does two nested sorted lookups.
    There are simpler methods for small and medium sized lists though.

    Attached Files:

    Vernon G and Thomas Kuriakose like this.
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    Perhaps,

    Try to change the "Output table" header E1:G1 as same as the "Source table" header A1:C1

    Then, in G2 enter :

    =DGET(A1:C157,C1,E1:F2)

    Regards
    Bosco

    Attached Files:

    Vernon G and Thomas Kuriakose like this.
  5. Vernon G

    Vernon G New Member

    Messages:
    8

    HI and thanks to all for quick responses. Differing solutions to the same question and all work...Excellent.

    I am looking to use this within a quote template, and I might have worded my question incorrectly so apologies....quantities can be anywhere from 1 to 10000 and I need to get the price as per the price break. so for example:

    60004 for qty 1 to 5 should be 235.53
    60004 for qty 6 to 10 should be 230.12
    60004 for qty 11 to 15 should be 227.50
    60004 for qty 16 to 10000 should be 222.45

    This table is refreshed via ODBC and will change once a month, but mainly the prices will change. We might also increase the range of codes in column A and also have different price breaks in column B.

    The headers as from our program Syspro are as follows:
    Column A StockCode
    Column B QtyBreak

    I have inserted the Column1 (not shown in my file) to concatenate some of the info so that in Column2 I do a lookup to a different page.

    Hope this makes sense:) and that you will find a solution.

    thanks again and kind regards
    Vernon





    Attached Files:

  6. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Vernon G
    Then You would modify Your QtyBreaks as below
    Screen Shot 2018-11-24 at 09.55.25.png
    use green values instead of red values.
  7. Vernon G

    Vernon G New Member

    Messages:
    8
    Thank you Vletm

    The pricing breaks given is what is set up in syspro and we just pull the info into excel...maybe I can add a colum to get back to the I green values....any suggestions

    Thanks again
    Vernon
  8. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Vernon G
    Do ALL StockCodes ALWAYS have SAME number of QtyBreaks?
    ( now four )
    and
    Do ALL QtyBreaks are ALWAYS same?
    ( now 5/10/15/1000 )
    >>
    You could Find & Replace manually those:
    from 5 to 1,
    from 10 to 6,
    from 15 to 10,
    from 10000 to 16
    Last edited: Nov 24, 2018
  9. Vernon G

    Vernon G New Member

    Messages:
    8
    Dear vletm

    For the StockCodes yes, but not so for the QtyBreaks. Even in my original file that I uploaded there are different QtyBreaks.
    I would also like that should the file change to include more StockCodes and different QtyBreaks that when we refresh the information via ODBC that the file still works.

    Hope this helps:)

    Thanks Again
    Vernon
  10. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    The somewhat complicated approach I posted as #3 deals with an arbitrary number of quantity/price breaks but it does assume that both the stock codes and quantities are sorted in ascending order.

    I f you feel you need to concatenate fields make sure you do not destroy the sorted nature of your search fields.
    Vernon G likes this.
  11. Vernon G

    Vernon G New Member

    Messages:
    8
    Me Again vletm

    For clarity in the file at present we have the ff QtyBreaks:

    5
    10
    15
    10000

    As well as

    10
    30
    50
    10000

    This might change in future but will always be four(4) breaks as per our administrator.

    I am looking for an "automatic fix" as if this works not everyone using the quote template will have access to all the pages, in order to protect and preserve the file and to ensure that the "inner workings"cannot be changed.

    Sorry to be a pain, but this will assist us greatly in our normal workday if it works.

    Thanks
    Vernon
  12. Vernon G

    Vernon G New Member

    Messages:
    8
    Going to give this a bash...and will advise...Thanks Peter
  13. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Vernon G
    Check this ...
    no need to change 'anything'!
    ... checked Stock Code should be valid by user!

    Attached Files:

    Thomas Kuriakose and Vernon G like this.
  14. Vernon G

    Vernon G New Member

    Messages:
    8
    HI vletm

    I changed the formula to =LOOKUP(LOOKUP(F2,J4:J7,H4:H7),J4:J7,I4:I7)

    and it works:)

    Now I just need to incorporate into my quote template. Will keep forum updated.

    Thanks to everyone for your assistance.

    This forum ROCKS

    Ciao for now(no guesses what i will be working on;))

    Vernon
  15. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Vernon G
    Nobody cannot guess what'll You working on.

    Of course,
    You can change that formulas as You want ...
    eg LOOKUP(F2,J4:J7,H4:H7).
    I would use as LOOKUP(F2,H4:H7,J4:J7),
    because You cannot know J4:J7 values before h4:H7 values or how?

    And
    cell G4, would be =IFERROR(MATCH(E2,A1:A157,0),0),
    then no need cell I2 at all.
  16. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    Try this formula solution without helper

    In G2, enter :

    =LOOKUP(F2,(N(+OFFSET($B$1,MATCH($E2,$A$2:$A$157,0)-1,0,4))+1)*(ROW($1:$4)^0-(MOD(ROW($1:$4),4)=1)),OFFSET($C$1,MATCH($E2,$A$2:$A$157,0),0,4))

    Regards
    Bosco

    Attached Files:

    Last edited: Nov 24, 2018
    Thomas Kuriakose and Vernon G like this.
  17. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    Firstlly a point specific to my method of post #3. If there are always 4 breaks, it is clearly not necessary to search for both the start and the end of the block relevant to the target stock code. The search to retain is the one for the final record (number = 'end') and then 'start' is always given by
    = end - 3

    As a more general concern, I am not convinced that your data is correctly arranged for a sorted search. You appear to have characterised the bands by the upper break point whereas Excel expects the lower break point because it returns the final value that is less than or equal to the search value. Typically the break points should start at 1 and no upper value is needed.

    Using MATCH / INDEX it is possible to look up a value and then return the price on the following row but that would be a somewhat perverse way of working.
  18. Vernon G

    Vernon G New Member

    Messages:
    8
    HI Everyone

    @vletm when i accessed your file it opened with the ff:
    Stock Code Qty Unit Price
    74004 10 1034.36

    When I changed qty to 1
    Stock Code Qty Unit Price
    74004 1 #N/A

    It returned a #N/A error and that was the only reason for changing the formula.

    @Peter Bartholomew I love your solution and it will definitely come in handy in the future. Regarding the data, i pull it like that from our Syspro server and can filter it in my query and will keep this in mind for future tasks.

    @bosco_yip your solution was the simplest to integrate into my quote template as no "helper" was necessary.

    I have tested my template and am happy to say that it works.

    Thanks to everyone for all your input as all your solutions were beyond my abilities and it would not have been possible without your excel skills.

    Top marks to this forum and its members, I will definitely be back to increase my knowledge and with problems as i get stuck.

    Kind Regards
    Vernon
  19. vannguyen

    vannguyen New Member

    Messages:
    4
    =LOOKUP(2,1/($A$2:$A$157=E2)/($B$2:$B$157<=F2),$C$2:$C$157)
  20. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    vannguyen

    That is a neat formula but there is a further catch. Vernon's quantity breaks are upper bounds rather than threshold quantities so column B has to be offset (and 10000 treated as 0 or 1). This would give
    =LOOKUP(2,1/(StockCode=Code)/((↑QtyBreak<=Qty)+(↑QtyBreak=10000)), UnitPrice)
    or, without defined names,
    =LOOKUP(2,1/($A$2:$A$157=E2)/(($B$1:$B$156<=F2)+($B$1:$B$156=10000)),$C$2:$C$157)

    p.s. I haven't used a direct cell reference for about 8 years now. Good to know I can still do it; even if I can't read what I have written :eek:.
  21. vannguyen

    vannguyen New Member

    Messages:
    4
    Thanks Mr Peter Bartholomew for your instruction
    Big thanks
  22. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    Hi,

    Please be noted that post #.20 formula will give wrong result in the qty of 5, 10 and 15

    For example, as per your requirement stated in post #.05 :

    60004 for qty 5 should be 235.53, but the above formula give 230.12
    60004 for qty 10 should be 230.12, but the above formula give 227.50
    60004 for qty 15 should be 227.50, but the above formula give 222.45

    and

    60001 for qty 1 to 5 should be 182.61, but the above formula give #N/A

    Regards
    Bosco
  23. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    500
    Bosco

    You are perfectly correct. I didn't know for certain whether the thresholds should be treated as the last of the previous block or the first of the new so I simply ignored the equality condition and let it come out how it would.
    =LOOKUP(2,
    1/(StockCode=Code)
    /((↑QtyBreak<Qty)+(↑QtyBreak=10000)),

    UnitPrice)

    I think that simply changing the inequality relationship on quantity to a strict inequality will give more acceptable results for the cases where the quantity is a level boundary.

    I was aware of the initialisation issue and simply set the validation dropdown to start at code 60002. There are a number of ways of setting up the initialisation, the simplest being to insert an extra data record along the lines
    60000 10000 0.00

    Other strategies might include using a test that allows either 10000 or "QtyBreak" such as (LEN(↑QtyBreak)>2). I felt there were too many rabbit holes to explore unless requested to do so. Another device I occasionally use with a regular range heading (not a Table) is set the field heading to 0 but then use number formatting to make it appear to be contain the appropriate text string.

Share This Page