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!!!!