using VLookup tables and then multiply another number

[Angela N2 Publishing]

Hi, everyone!! I'm excited about this forum!! I love Excel and it's endless features!
I was wondering if anyone could help me out ... hopefully this is an easy question/answer!

I'm trying to create an Excel Spreadsheet with a vlookup table, and I can't figure out the correct formula - but i'm on the brink!

What I'm trying to do ... I want to figure out the total weight of a Fedex shipment, based off of the page count and print count of the publications we are Fedex'ing.
For example .... a 40 page publication is 0.3225 lbs each ... and if there are 1000 copies printed ... the total would be 322.5 pounds for the Fedex Freight shipment

The vlookup table will be the the page counts with how much they weight ... and that multipled by the print count.

Vlookup table:
[TABLE="width: 261"]
<tbody>[TR]
[TD]Pages[/TD]
[TD]weight[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD].1290 lbs[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD].1625 lbs[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD].1935 lbs[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD].2257 lbs[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD].2580 lbs[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD].2902 lbs[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD].3225 lbs[/TD]
[/TR]
</tbody>[/TABLE]


Multiple by:
[TABLE="width: 173"]
<tbody>[TR]
[TD]PageCount[/TD]
[TD]PrintCount[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]395[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]829[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]1565[/TD]
[/TR]
[TR]
[TD]68[/TD]
[TD]931[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]583[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]689[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]1637[/TD]
[/TR]
</tbody>[/TABLE]


So ... in this example ... a 40 page publication weighs 0.3225 each ... and there are 583 of this particular one:
583 x .3225 (40 page) = 188 pounds for Fedex shipping

I hope that makes sense and I dind't over-complicate it!!

What's the formula to be able to refer to a vlookup table with pages and weights ... and then multiple by print count ... create a total weight ... and then have a column that I can click and drag, and it will populate all the correct total weights for a Fedex shipment???

THANK YOU!!

 

[DanteAmor]

Try this formula


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:88.4px;" /><col style="width:98.85px;" /><col style="width:96px;" /><col style="width:31.37px;" /><col style="width:34.22px;" /><col style="width:76.04px;" /><col style="width:107.41px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; ">PageCount</td><td style="background-color:#92d050; ">PrintCount</td><td style="background-color:#92d050; ">Result (lbs)</td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; ">Pages</td><td style="background-color:#92d050; ">weight (lbs)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">84</td><td style="text-align:right; ">395</td><td style="text-align:right; ">267.7310</td><td > </td><td > </td><td style="text-align:right; ">16</td><td style="text-align:right; ">0.1290</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">32</td><td style="text-align:right; ">829</td><td style="text-align:right; ">213.8820</td><td > </td><td > </td><td style="text-align:right; ">20</td><td style="text-align:right; ">0.1625</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">28</td><td style="text-align:right; ">1565</td><td style="text-align:right; ">353.2205</td><td > </td><td > </td><td style="text-align:right; ">24</td><td style="text-align:right; ">0.1935</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">68</td><td style="text-align:right; ">931</td><td style="text-align:right; ">510.7466</td><td > </td><td > </td><td style="text-align:right; ">28</td><td style="text-align:right; ">0.2257</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">40</td><td style="text-align:right; ">583</td><td style="text-align:right; ">188.0175</td><td > </td><td > </td><td style="text-align:right; ">32</td><td style="text-align:right; ">0.2580</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">36</td><td style="text-align:right; ">689</td><td style="text-align:right; ">199.9478</td><td > </td><td > </td><td style="text-align:right; ">36</td><td style="text-align:right; ">0.2902</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">32</td><td style="text-align:right; ">1637</td><td style="text-align:right; ">422.3460</td><td > </td><td > </td><td style="text-align:right; ">40</td><td style="text-align:right; ">0.3225</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">44</td><td style="text-align:right; ">0.3548</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">48</td><td style="text-align:right; ">0.3871</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">52</td><td style="text-align:right; ">0.4194</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">56</td><td style="text-align:right; ">0.4517</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">60</td><td style="text-align:right; ">0.4840</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">64</td><td style="text-align:right; ">0.5163</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">68</td><td style="text-align:right; ">0.5486</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">72</td><td style="text-align:right; ">0.5809</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">76</td><td style="text-align:right; ">0.6132</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">80</td><td style="text-align:right; ">0.6455</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">84</td><td style="text-align:right; ">0.6778</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">88</td><td style="text-align:right; ">0.7101</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">92</td><td style="text-align:right; ">0.7424</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">96</td><td style="text-align:right; ">0.7747</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">100</td><td style="text-align:right; ">0.8070</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IFERROR(B2*VLOOKUP(A2,$F$2:$G$23,2,0),"")</td></tr></table></td></tr></table>

 

[Angela N2 Publishing]

That is EXACTLY what I need to do ... but when I recreate your spreadsheet ---> copy and paste in the formula you gave me in the Cell C2 ---> and hit enter, the cell is blank. Am I missing a key part to how you actually got results?

 

[Angela N2 Publishing]

Nevermind!! I figured out what was wrong!! I had the "lbs" in the weight column ... needs to just be the number.

WORKS GREAT!! THANKS!!

 

[DanteAmor]

Nevermind!! I figured out what was wrong!! I had the "lbs" in the weight column ... needs to just be the number.

WORKS GREAT!! THANKS!!

I'm glad to help you. Thanks for the feedback.