How to get pricing with multi tiered pricing from workbook

I have a worksheet that has the current prices for each item. This worksheet has various prices based on the size of your order for that product.
Here are the columns:
Item
Description
Min Order (the minimum that you can order of that item)
Base Price (price you pay if you order the min up to the next level)
Discount1 (QTY (the number of items to get the first price break)
Discount1 (Price Price for the second tier of pricing)
Discount2 (QTY (the number of items to get the second price break)
Discount2 (Price Price for the third tier of pricing)
Discount3 (QTY (the number of items to get the third price break)
Discount3 (Price Price for the forth tier of pricing)

Sample of data:
Item|Description|MinOrder|BasePrice|Disc1|Disc1Price|Disc2|Disc2Price|Disc3|Disc3Price
Wig1|Widget1|1|$10.15|10|$9.60|25|$9.10|50|$8.65
Wig2|Widget2|1|$60.00|2|$54.40|5|$50.40|10|$46.40
Wig3|Widget3|1|$60.00|2|$54.40|5|$50.40|10|$46.40
Wig4|Widget4|5|$2.25|10|$2.05|100|$1.85|500|$1.75
Wig5|Widget5|5|$4.85|10|$4.50|100|$4.15|500|$3.90

What I would like to do is to have a module that would return the price for the number of items ordered for a line item on an orderform

Example
I want to order 14 Wig1, it would return 9.60
If I place an order for 2 wig5, up comes a messagebox that lets me know the minimum
Then if I correct that, and order 6 wig5 if would return 4.85
And if I order 1000 wig4, it would return 1.75

How would I code this? I can use VLOOKUP easy enough to get the BASE price, but not sure how to get to and test for the other columns.

Thanks!
Bruce
BrujAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
reitzenCommented:
=IF(C10<VLOOKUP(B10,tbl,3,FALSE),"Minimum required "&VLOOKUP(B10,tbl,3,FALSE),IF(AND(C10>=VLOOKUP(B10,tbl,3,FALSE),C10<VLOOKUP(B10,tbl,5,FALSE)),VLOOKUP(B10,tbl,4,FALSE),IF(AND(C10>=VLOOKUP(B10,tbl,5,FALSE),C10<VLOOKUP(B10,tbl,7,FALSE)),VLOOKUP(B10,tbl,6,FALSE),IF(AND(C10>=VLOOKUP(B10,tbl,7,FALSE),C10<VLOOKUP(B10,tbl,9,FALSE)),VLOOKUP(B10,tbl,8,FALSE),VLOOKUP(B10,tbl,10,FALSE)))))

Open in new window


Using the 10 columns you described, this will return the correct value based upon the quantity ordered.

I used nested IF's to test if the quantity ordered was between MinOrder and Disc1, then Disc1 and Disc2, etc.

There is probably a more elegant approach to this using an array formula or something, but that would be above my pay grade.  :-P

Good luck
reitzenCommented:
PS.  I used a named range "tbl" to represent your data table A1:J6
BrujAuthor Commented:
Thanks!
I was looking for a function, which you guys guided me on how to write it!
It is here:
Public Function GetPrice(myItem As String, myQTY As Long) As Double
Dim myRange As Range

Set myRange = ThisWorkbook.Sheets("Sheet1").Range("A:k")
If myQTY >= Application.WorksheetFunction.VLookup(myItem, myRange, 9, False) Then
GetPrice = Application.WorksheetFunction.VLookup(myItem, myRange, 10, False)
ElseIf myQTY >= Application.WorksheetFunction.VLookup(myItem, myRange, 7, False) Then
GetPrice = Application.WorksheetFunction.VLookup(myItem, myRange, 8, False)
ElseIf myQTY >= Application.WorksheetFunction.VLookup(myItem, myRange, 5, False) Then
GetPrice = Application.WorksheetFunction.VLookup(myItem, myRange, 6, False)
ElseIf myQTY >= Application.WorksheetFunction.VLookup(myItem, myRange, 3, False) Then
GetPrice = Application.WorksheetFunction.VLookup(myItem, myRange, 4, False)
Else
MsgBox "Minimum order is " & Application.WorksheetFunction.VLookup(myItem, myRange, 3, False)
End If



End Function

Open in new window


Now I just need to add some error checking (like if part is not found, or an item does not have a discounted price etc....)

Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Images and Photos

From novice to tech pro — start learning today.