Try building your table like this:

Qty 1 100 400 2000
SKU0 5 16 4 13
SKU1 3 12 7 14
SKU2 9 2 11 8
SKU3 15 10 6 1

The column headers are the first qty that gets into that price group.

Then you can use a formula like:

=INDEX(B2:E5,MATCH(x1,A2:A5,0),MATCH(y1,B1:E1,1))

Where x1 contains the SKU# and y1 contains the qty.

Take a look at Debra Dalgleish's notes for more info about =index(match()):
http://www.contextures.com/xlFunctions03.html

Des wrote:

Hi
I have a sliding scale price list (in a separate table) ie

Qty 1-99 100-399 400-1999 2000
Price 28.00 24.00 22.00 21.00
SKU1
SKU2
SKU3 etc

In my database, I need to match the SKU, AND the qty and calculate what the
price should be, and then match it against what the actual ship price was, ie

SKU Ship Qty Ship Amount
SKU1 35 980.00
SKU1 400 11200.00

I'm trying to establish which SKU's were shipped at the correct price and
which not, and find the variance between the two.

I know it's a nested statement, since I have multiple SKU's and multiple
prices per qty break.

Please could someone assist - have been through the Q&A's, but couldn't find
anything to put me on the right track.

Many thanks
Des

--

Dave Peterson