vlookup question

[Dave Johnson]

My wife owns a t-shirt company and I'm trying to help her write a
spreadsheet that will calculate price per shirt printed using a vlookup
formula.

The table has 10 rows of quantities in the left column and 6 colums of
colors 1-6 across the top row. As long as I only use one vlookup (for a one
sided print job) it works perfectly. Where I'm getting stuck is when I try
to add a second vlookup for the number of colors to be printed on the back
of each shirt.

As long as I have a quantity of 1 to 6 it works fine. It's when the value is
0 or blank that something is going very wrong. The result is very high, $106
per shirt when it should be $3.30.

How do I get my formula to ignore the second vlookup if the value is 0?

=((VLOOKUP(B12,D6:J16,B9+1)+(B16/B12)+B8)+VLOOKUP(B12,D6:J16,B10+1))/B6

1 2 3 4 5 6
1 20.00 40.00 60.00 100.00 125.00 150.00
12 5.00 6.00 7.00 8.00 9.00 10.00
24 2.00 2.50 3.00 3.50 4.00 4.50
48 0.95 1.20 1.50 1.80 2.10 2.40
72 0.85 1.15 1.35 1.55 1.75 1.90
144 0.75 1.05 1.25 1.40 1.55 1.75
288 0.65 1.00 1.20 1.35 1.50 1.60
576 0.50 0.90 1.10 1.25 1.40 1.50
1200 0.45 0.55 0.65 0.75 0.85 0.95
3000 0.40 0.45 0.55 0.65 0.75 0.85


Thanks for any help with this.

Dave

 

[Bob Phillips]

What's in B6,B8, B9, B12 and B16?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Max]

Assuming the price table is in Sheet1, in A1:G11

In Sheet2,

With quantities listed in A2 down, colors listed in B2 down

To retrieve the unit-prices, put in C2:
=IF(OR(ISNA(MATCH(A2,Sheet1!$A$1:$A$11,1)),ISNA(MATCH(B2,Sheet1!$A$1:$G$1,0)
)),"",INDEX(Sheet1!$A$1:$G$11,MATCH(A2,Sheet1!$A$1:$A$11,1),MATCH(B2,Sheet1!
$A$1:$G$1,0)))

Copy C2 down as far as required

Sample construct at:
http://cjoint.com/?msbku8PWTL
Retrieving UnitPrices From Reference Table_DaveJohnson_gen.xls

 

[Dave Johnson]

B6 is the desired margin for the job, B8 is the cost of the shirt, B9 is the
number of colors front, B12 is the total number of shirts, and B16 is an
charge for flashing a color if needed. You didn't ask about B10, but that's
the number of colors on the back of the shirt. This is where I'm running
into trouble. When B10 is nil, I get a rediculous result.

 

[Dave Johnson]

I think I posed my question badly. This is my first time to post here and in
fact my first attempt to use a table lookup in Excel. I started with someone
else's templet and with a look at the help file and some trial and error got
to where I am. It seems like I'm close because before I added the
calculations for the number of colors on the back the sheet seemed to work
fine.

To clarify some things:

B6 Desired Margin
B8 Cost of blank shirt
B9 # Colors Front
B10 # Colors Back
B12 # Total Shirts
B16 # Flash charges
D6:J16 is my table of wholesale prices (see below)

Maybe this will help. Again, thanks for any help.

Dave

 

[kcc]

The lookup returns the shirt count when the color count is zero.
I would add a 0 color column between the count and 1 color, then change +1
to +2 in your formula.
kcc

 

[Dave Johnson]

Thank you kcc!! Adding the 0 quantity row did the trick!