How is your pricing portion set up? Do you have quantities down the column,
and part numbers as row headers? This would be an easy way to do it. Because,
then, no matter the quantity discounts, as long as you set up your table
properly, you'll be good.
I.E.:

Part1 Part2 Part3
0 25.00 75.00 5.25
25 23.75 75.00 5.25
30 23.75 67.50 5.25
50 23.75 67.50 4.95

Basically, in this scenario, Part1 is $25.00, unless they order more than
25, Part2 is $75.00, unless they order 30 or more, and Part3 is $5.25 unless
they order 50 or more. The key is to ensure the 'quantities' down the left
column are in ascending order, and starting with 0.

So your lookup would be:
=VLOOKUP(qty,TblPrice!$A$1:$D$5,MATCH(part#,TblPri ce!A1:D1,0),1)

This assumes your pricing table is a separate tab. Note the last '1' in the
VLOOKUP, this will allow it to search for the best 'qty' in left column,
without going over.

Hope this helps.



--
John C


"Martin Panter" wrote:

Put simply I am trying to create a formula in TAB 1, that if I enter a 'Part
Number' in cell A1, Vlookup will return its description in cell A2 (no
problems so far), and then if I enter a quantity in A3, cell A4 will identify
the correct band from the following ranges ,25, 26-100, 101-500, 501-1000 or
1000 and return the correct unit price for the part number and quantity
break - which is identified and held in a separate table (on TAB 2),
alongside the Part Number and Descriptions.

Martin