Lookup

[Tornado1981]

hi,

I would really appreatiate if u could help me solving that

What i want to do is :

Take the value of B5 and enter the table Y7:AC7 (Clicks_Prices) and display in J5 the sum of :
multiplying the corresponding value to B5 in col. 2 (Z) by the value in E5
multiplying the corresponding value to B5 in col. 3 (AA) by the value in F5
multiplying the corresponding value to B5 in col. 4 (AB) by the value in G5

I used the formula shown in the formula bar, but the problem is that if any of the cells (E5,F5 or G5) is blank, excel consider it as 1 and multiply it by the corresponding value in Clicks_Prices table and add it to the sum

So please is there any way to solve that

Thank you

 

[VoG]

You have posted an image that is too small to decipher. Images are not helpful because we cannot copy your data and paste into Excel.

Try posting a shot of your sheet using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[Tornado1981]

Thanks VoG for ur support

Here it is again

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 90px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 71px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 79px"><COL style="WIDTH: 87px"><COL style="WIDTH: 69px"><COL style="WIDTH: 114px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 100px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 90px"><COL style="WIDTH: 64px"><COL style="WIDTH: 67px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD><TD>AB</TD><TD>AC</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center" rowSpan=3>Date</TD><TD style="TEXT-ALIGN: center" rowSpan=3>Membership</TD><TD style="TEXT-ALIGN: center" colSpan=2>No. of Referrals</TD><TD style="TEXT-ALIGN: center" colSpan=5>No. of Clicks</TD><TD style="TEXT-ALIGN: center" colSpan=3>Income</TD><TD style="TEXT-ALIGN: center" colSpan=5>Expenses</TD><TD> </TD><TD style="TEXT-ALIGN: center" colSpan=5>Clicks Prices</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Direct</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Rented</TD><TD style="TEXT-ALIGN: center" colSpan=3>Mine</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Direct refs</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Rented refs</TD><TD style="TEXT-ALIGN: center" rowSpan=2>My clicks</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Referrals clicks</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Total</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Rent</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Extend</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Autopay</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Recycle</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Total</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">Standard</TD><TD style="TEXT-ALIGN: center">Mini</TD><TD style="TEXT-ALIGN: center">Tiny</TD><TD> </TD><TD style="TEXT-ALIGN: left" rowSpan=3>Membership</TD><TD style="TEXT-ALIGN: center" colSpan=4>Clicks</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">1/4/2011</TD><TD style="TEXT-ALIGN: center">Standard</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center" colSpan=3>Mine</TD><TD style="TEXT-ALIGN: center" rowSpan=2>My refs</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">2/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">Standard</TD><TD style="TEXT-ALIGN: center">Mini</TD><TD style="TEXT-ALIGN: center">Tiny</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">3/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">Golden</TD><TD style="TEXT-ALIGN: center">0.01</TD><TD style="TEXT-ALIGN: center">0.003</TD><TD style="TEXT-ALIGN: center">0.001</TD><TD style="TEXT-ALIGN: center">0.01</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">4/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">Standard</TD><TD style="TEXT-ALIGN: center">0.01</TD><TD style="TEXT-ALIGN: center">0.003</TD><TD style="TEXT-ALIGN: center">0.001</TD><TD style="TEXT-ALIGN: center">0.005</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">5/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">6/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">7/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">8/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left" rowSpan=2>Referrals</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Golden</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Standard</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">9/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left">10/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">0 - 250</TD><TD style="TEXT-ALIGN: center">$0.21 </TD><TD style="TEXT-ALIGN: center">$0.25 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left">11/4/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">251 - 500</TD><TD style="TEXT-ALIGN: center">$0.21 </TD><TD style="TEXT-ALIGN: center">$0.26 </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left">12/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">501 - 750</TD><TD style="TEXT-ALIGN: center">$0.22 </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left">13/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">751 - 1000</TD><TD style="TEXT-ALIGN: center">$0.23 </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: left">14/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">1001 - 1250</TD><TD style="TEXT-ALIGN: center">$0.24 </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: left">15/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">1251 - 1500</TD><TD style="TEXT-ALIGN: center">$0.25 </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: left">16/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">1501 - 1750</TD><TD style="TEXT-ALIGN: center">$0.26 </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: left">17/4/2011</TD><TD style="TEXT-ALIGN: center">-</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left">Over 1750</TD><TD style="TEXT-ALIGN: center">$0.28 </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>J5</TD><TD>=IF(B5="-","",IF(COUNT(E5:G5),SUM(PRODUCT(E5,VLOOKUP(B5,Clicks_Prices,2)),PRODUCT(F5,VLOOKUP(B5,Clicks_Prices,3)),PRODUCT(G5,VLOOKUP(B5,Clicks_Prices,4))),""))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4