Re: vlookup question

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(MA TCH(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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Dave Johnson" <(E-Mail Removed)> wrote in message
news:TS0pf.618$(E-Mail Removed)...
> 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
>
>
>
>