Help with spreadsheet design - if/then or vlookup with mulitple scenarios

[jshrader]

The problem that i am trying to solve is that I want to create an invoice that once I put in an item# and a quantity, then the system returns the correct price. When we quote prices to a customer, my salespeople will quote the same price for an item whether it is for qty=1 or qty=250. So on large deals, I lose because I am too expensive. I am willing to give the customer a lower price (and therefore lower margin) if I am selling more units.

I am having trouble getting started and I am hoping you can point me in the right direction.....

If you can see in the attached spreadsheet, I can have an item with up to 10 different prices, based on the quantity. I first thought that I should do if, then but then I can only have a maximum of 7 nested (so therefore 7 price levels). then I was thinking of vlookup, but then I need to look at two variables, the first being the item# and then the quantity. When looking at the quantity it won't be an exact match. For example, if you look at the attachment, buying 50 of item#1 or 99 of item#1 (or any number >=50 and <100) should bring the same price, that is $80.00.

So do I start with defining each items on multiple rows (a3:g13) or do I define each item on one row with addition columns (a19:p20)?

I woudl really appreciate you thoughts on ho wto get started!

Thanks in advance for your guidance!!!!

[vorabha]

Hi Jashrader

1) their are prices depends on Minimun Qty req..
2) Create one Unique column like i created (attached) SKU which will be from your exisiting columns only.. thus your pricing then will be possible to identify
3) your first option is correct, i donot see any point in creating items list 2nd option
4) then allow user to select items thru datavalidation list
5) and user Vlookup for price populate



Vora

[DonkeyOte]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Specifically please remove "Help with Spreadsheet Design -".