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

  • 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

Vernon G

New Member
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
 

Attachments

Vernon G

New Member
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

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





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
 

Attachments

Vernon G

New Member
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
 

vletm

Excel Ninja
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:

Vernon G

New Member
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
 

Peter Bartholomew

Well-Known Member
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

New Member
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
 

Vernon G

New Member
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.
Going to give this a bash...and will advise...Thanks Peter
 

Vernon G

New Member
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
 

vletm

Excel Ninja
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.
 

bosco_yip

Excel Ninja
...........
This might change in future but will always be four(4) breaks as per our administrator..............
Vernon
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
 

Attachments

Last edited:

Peter Bartholomew

Well-Known Member
This might change in future but will always be four(4) breaks as per our administrator.
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.
 

Vernon G

New Member
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
 

Peter Bartholomew

Well-Known Member
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:.
 

vannguyen

New Member
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:.
Thanks Mr Peter Bartholomew for your instruction
Big thanks
 

bosco_yip

Excel Ninja
Thanks Mr Peter Bartholomew for your instruction
Big thanks
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
 

Peter Bartholomew

Well-Known Member
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.
 
Top