Hi!

Your setup is not ideal!

I would group all the Qty's and prices.

Item......Q1.....Break1.....Break2...........Price ......Price1.....Price2
A............1..........5.............10.......... ......100..........90...........80
B............1..........8.............15.......... ......200.........180.........160
C............1.........10............20........... .....300.........280.........250

Here's a sample file.

Sample_lookup2.xls 13.5kb

http://cjoint.com/?joxRGgpo3U

Biff

wrote in message
oups.com...
Good afternoon - I have a order entry file I'm trying to enable the
possibility for offering quantity breaks. However, the different items
to be sold have different levels of qty breaks. For example:

Item Qty Price Break1 Break1Price Break2 Break2 Price
a 1 100 5 90 10
80
b 1 200 8 180 15
160
c 1 300 10 280 20
250

If the user enters the item, I am hoping to have it lookup the item,
and choose the proper unit price based on the quantity offered. For
example, 10 of item b would cost $180 each.

My problem seems to be the non-contiguous setup of the qty breaks -
i've tried named ranges, vlookup, index, and match functions,
with/without named ranges - all unsuccessfully.

Any ideas gurus?