Sumif, And Statements - multiple conditions

[Des]

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]

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]

Many thanks Dave, just tried that and it seems to work great on test - will
now apply it to my worksheet!
Appreciate the prompt reply
Des

 

[Des]

Hi
Tried to apply this to my work and I'm getting a return of the SKU No and
not the price.

=INDEX(NSN!$G$10:$K$89,MATCH('06 SWMS
GSA'!$S15,NSN!$G$10:$G$89,0),MATCH('06 SWMS GSA'!$Y15,NSN!$H$9:$K$9,1))

NSN G10:K89 is my pricing table tab and set out as you suggested
06 SWMS GSA is my main data table tab S15 is the SKU column and Y12 is the
Ship Qty.

I tried another way, using Debra's example where "Pricing" is a named table
(ie NSN G10:K89)

=INDEX(Pricing,MATCH('06 SWMS GSA'!S12,INDEX(Pricing,,1),0),MATCH('06 SWMS
GSA'!Y12,INDEX(Pricing,1,),0))

I'm getting a #N/A error on this one.

What am I doing incorrectly?

I did get it to work on a test (although of course it's a much simpler
example), which looks like this

Qty 1 100 400 2000
S1 5 16 4 13
S2 3 9 7 14
S3 9 2 10 8
S4 15 10 6 1

SKU Qty Price
S4 2500 1
S1 10 5
S2 500 7
S3 450 10

=INDEX($B$2:$E$5,MATCH(A8,$A$2:$A$5,0),MATCH(B8,$B$1:$E$1,1))

Would appreciate any further assistance.
Many thanks
Des