Multiplication Problem ...Suggestion required

Z

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi,


<table border="0" cellpadding="0" cellspacing="0" width="554"><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:74pt" height="20" width="99">SYMBOL</td> <td class="xl65" style="border-left:none;width:60pt" width="80">TIMESTAMP</td> <td class="xl65" style="border-left:none;width:48pt" width="64">OPEN</td> <td class="xl65" style="border-left:none;width:48pt" width="64">HIGH</td> <td class="xl65" style="border-left:none;width:48pt" width="64">LOW</td> <td class="xl65" style="border-left:none;width:71pt" width="95">CONTRACTS</td> <td class="xl65" style="border-left:none;width:66pt" width="88">OPEN_INT</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">BANKNIFTY-I</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">11299.9</td> <td class="xl65" style="border-top:none;border-left:none">11346.95</td> <td class="xl65" style="border-top:none;border-left:none">11212.25</td> <td class="xl65" style="border-top:none;border-left:none">39508</td> <td class="xl65" style="border-top:none;border-left:none">969625</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">BANKNIFTY-II</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">11340.05</td> <td class="xl65" style="border-top:none;border-left:none">11354.75</td> <td class="xl65" style="border-top:none;border-left:none">11240</td> <td class="xl65" style="border-top:none;border-left:none">640</td> <td class="xl65" style="border-top:none;border-left:none">22625</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">BANKNIFTY-III</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">CNXIT-I</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">6668.5</td> <td class="xl65" style="border-top:none;border-left:none">6729.95</td> <td class="xl65" style="border-top:none;border-left:none">6660</td> <td class="xl65" style="border-top:none;border-left:none">81</td> <td class="xl65" style="border-top:none;border-left:none">15650</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">CNXIT-II</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">50</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">CNXIT-III</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> </tr> </tbody></table>
The above table is the original data. I have one more excel file stored in E Drive which contains the Lot Size values for each symbol but there is only one symbol reference for all the Three expiry dates because the lot size is same for Each contract be it First Month Contract (-I),Second Month Contract (-II) or Third Month Contract (-III)

Example of Lot size data

<table border="0" cellpadding="0" cellspacing="0" width="163"><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:74pt" height="20" width="99">SYMBOL </td> <td class="xl65" style="width:48pt" align="right" width="64">11-Jul</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY </td> <td align="right">25</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY </td> <td align="right">20</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NIFTY </td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50</td> <td align="right">150</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT </td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Symbol </td> <td class="xl65" align="right">11-Jul</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">3IINFOTECH</td> <td align="right">8000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">TRIVENI </td> <td align="right">2000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABAN </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABB </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABGSHIP </td> <td align="right">1000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABIRLANUVO</td> <td align="right">500</td> </tr> </tbody></table>
Example of desired output in the original table

BANKNIFTY-I 39508*25
BANKNIFTY-II 640 *25
BANKNIFTY-III 0 *25
CNXIT-I 81 *50

Etc..

I want to multiply the Values of Contract Column in the orignial table with the Lot size values Referred to in another excelsheet.

Kindly give your suggestions

Regards,
Zaska
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
In H2 enter and copy down:

=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&Sheet2!$A$1:$A$13,"#"&A2),Sheet2!$B$1:$B$13)

where F2 houses the contract value of 39508, A2 BANKNIFTY-I.

Sheet2!$A$1:$A$13 houses:

<TABLE style="WIDTH: 65pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=87><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3100" width=87><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=87>SYMBOL</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>BANKNIFTY</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>MINIFTY</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>NIFTY</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>NFTYMCAP50</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>CNXIT</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Symbol</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>3IINFOTECH</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>TRIVENI</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>ABAN</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>ABB</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>ABGSHIP</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>ABIRLANUVO</TD></TR></TBODY></TABLE>

Sheet2!$B$1:$B$13 houses:

<TABLE style="WIDTH: 49pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=65><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2304" width=65><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=65 align=right>11-Jul</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>25</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>20</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>50</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>150</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>50</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>11-Jul</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>8000</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>2000</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>500</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>500</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>1000</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>500</TD></TR></TBODY></TABLE>
 
Upvote 0
Sir,

I got #N/A error

Formula Used

Code: 
=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&Sheet2!$A$1:$A$13,"#"&A2),Sheet2!$B$1:$B$13)

I just gave  a sample data and the actual data contains more than 13 symbols

Regards,
Zaska
 
Upvote 0
Sir,

Only column returned the correct result

I didn't understand why u are using 9999999999999 + 307 in your formula

<table border="0" cellpadding="0" cellspacing="0" width="555"><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="mso-width-source:userset;mso-width-alt:2633;width:54pt" width="72"> <col style="width:48pt" span="6" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:74pt" height="20" width="99">SYMBOL</td> <td style="width:54pt" width="72">TIMESTAMP</td> <td style="width:48pt" width="64">OPEN</td> <td style="width:48pt" width="64">HIGH</td> <td style="width:48pt" width="64">LOW</td> <td style="width:48pt" width="64">CONTRACTS</td> <td colspan="2" style="mso-ignore:colspan;width:96pt" width="128">OPEN_INT</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-I</td> <td class="xl65" align="right">20110701</td> <td align="right">11299.9</td> <td align="right">11346.95</td> <td align="right">11212.25</td> <td align="right">39508</td> <td align="right">969625</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-II</td> <td class="xl65" align="right">20110701</td> <td align="right">11340.05</td> <td align="right">11354.75</td> <td align="right">11240</td> <td align="right">640</td> <td align="right">22625</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-III</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-I</td> <td class="xl65" align="right">20110701</td> <td align="right">6668.5</td> <td align="right">6729.95</td> <td align="right">6660</td> <td align="right">81</td> <td align="right">15650</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-II</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">50</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-III</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY-I</td> <td class="xl65" align="right">20110701</td> <td align="right">5685</td> <td align="right">5699</td> <td align="right">5616.1</td> <td align="right">31575</td> <td align="right">677440</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY-II</td> <td class="xl65" align="right">20110701</td> <td align="right">5697.4</td> <td align="right">5710</td> <td align="right">5633</td> <td align="right">2389</td> <td align="right">98760</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY-III</td> <td class="xl65" align="right">20110701</td> <td align="right">5710.9</td> <td align="right">5719</td> <td align="right">5651</td> <td align="right">335</td> <td align="right">4080</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50-I</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50-II</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50-III</td> <td class="xl65" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NIFTY-I</td> <td class="xl65" align="right">20110701</td> <td align="right">5700.2</td> <td align="right">5702</td> <td align="right">5616.25</td> <td align="right">256486</td> <td align="right">20848500</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NIFTY-II</td> <td class="xl65" align="right">20110701</td> <td align="right">5676</td> <td align="right">5717</td> <td align="right">5632.25</td> <td align="right">4937</td> <td align="right">508300</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NIFTY-III</td> <td class="xl65" align="right">20110701</td> <td align="right">5656</td> <td align="right">5774.85</td> <td align="right">5653</td> <td align="right">857</td> <td align="right">31800</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">3IINFOTECH-I</td> <td class="xl65" align="right">20110701</td> <td align="right">45.8</td> <td align="right">46.55</td> <td align="right">44.5</td> <td align="right">526</td> <td align="right">11560000</td> <td align="right">4208000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">3IINFOTECH-II</td> <td class="xl65" align="right">20110701</td> <td align="right">45.55</td> <td align="right">46.5</td> <td align="right">45.55</td> <td align="right">5</td> <td align="right">128000</td> <td align="right">40000</td> </tr> </tbody></table>
Thanks
 
Upvote 0
#N/A will occur only when A2 (on the data sheet) does not contain any symbol from Sheet2...

Maybe you have stray spaces around entries on the data sheet: Just to check, Does this...

=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&Sheet2!$A$1:$A$13,"#"&TRIM(A2)),Sheet2!$B$1:$B$13)

give the expected results?
 
Last edited:
Upvote 0
Sir,

The data has all the symbols...Even then it turned #N/A

The only difference is that sheet2 values don't contain "-I","-II","-III"


Regards,

Zaska
 
Upvote 0
Sir,

I tried with Trim it was better than the first one, but still Errors. I noticed one thing when i retype the data in sheet2 i got result for few symbols. The formula is fine there is something wrong with the Data , i couldn't figure . I tried using find and replace to remove any spaces between symbols.

<table border="0" cellpadding="0" cellspacing="0" width="564"><col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> <col style="width:48pt" span="7" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:87pt" height="20" width="116">ABGSHIP-I</td> <td class="xl63" style="width:48pt" align="right" width="64">20110701</td> <td style="width:48pt" align="right" width="64">358</td> <td style="width:48pt" align="right" width="64">366.95</td> <td style="width:48pt" align="right" width="64">357</td> <td style="width:48pt" align="right" width="64">192</td> <td style="width:48pt" align="right" width="64">3276000</td> <td style="width:48pt" align="right" width="64">192000</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ABGSHIP-II</td> <td class="xl63" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2000</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ABGSHIP-III</td> <td class="xl63" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ABIRLANUVO-I</td> <td class="xl63" align="right">20110701</td> <td align="right">912.4</td> <td align="right">912.4</td> <td align="right">899</td> <td align="right">43</td> <td align="right">1003500</td> <td align="right">21500</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ABIRLANUVO-II</td> <td class="xl63" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">1500</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ABIRLANUVO-III</td> <td class="xl63" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ACC-I</td> <td class="xl63" align="right">20110701</td> <td align="right">950.3</td> <td align="right">964.5</td> <td align="right">939.75</td> <td align="right">2647</td> <td align="right">1030250</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ACC-II</td> <td class="xl63" align="right">20110701</td> <td align="right">950.1</td> <td align="right">961.3</td> <td align="right">938.45</td> <td align="right">202</td> <td align="right">82750</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ACC-III</td> <td class="xl63" align="right">20110701</td> <td align="right">945.95</td> <td align="right">945.95</td> <td align="right">938.5</td> <td align="right">59</td> <td align="right">14750</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ADANIENT-I</td> <td class="xl63" align="right">20110701</td> <td align="right">722</td> <td align="right">722</td> <td align="right">696.05</td> <td align="right">380</td> <td align="right">1062000</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ADANIENT-II</td> <td class="xl63" align="right">20110701</td> <td align="right">704.7</td> <td align="right">704.7</td> <td align="right">704.7</td> <td align="right">1</td> <td align="right">500</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ADANIENT-III</td> <td class="xl63" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ADANIPOWER-I</td> <td class="xl63" align="right">20110701</td> <td align="right">111.05</td> <td align="right">111.05</td> <td align="right">110.2</td> <td align="right">184</td> <td align="right">4726000</td> <td align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">ADANIPOWER-II</td> <td class="xl63" align="right">20110701</td> <td align="right">110.85</td> <td align="right">110.85</td> <td align="right">110.85</td> <td align="right">1</td> <td align="right">4000</td> <td align="center">#N/A</td> </tr> </tbody></table>
 
Upvote 0
zaska said:
Sir,

I tried with Trim it was better than the first one, but still Errors. I noticed one thing when i retype the data in sheet2 i got result for few symbols. The formula is fine there is something wrong with the Data , i couldn't figure . I tried using find and replace to remove any spaces between symbols.

<TABLE border=0 cellSpacing=0 cellPadding=0 width=564><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 87pt; HEIGHT: 15pt" class=xl64 height=20 width=116>ABGSHIP-I</TD><TD style="WIDTH: 48pt" class=xl63 width=64 align=right>20110701</TD><TD style="WIDTH: 48pt" width=64 align=right>358</TD><TD style="WIDTH: 48pt" width=64 align=right>366.95</TD><TD style="WIDTH: 48pt" width=64 align=right>357</TD><TD style="WIDTH: 48pt" width=64 align=right>192</TD><TD style="WIDTH: 48pt" width=64 align=right>3276000</TD><TD style="WIDTH: 48pt" width=64 align=right>192000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ABGSHIP-II</TD><TD class=xl63 align=right>20110701</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>2000</TD><TD align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ABGSHIP-III</TD><TD class=xl63 align=right>20110701</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ABIRLANUVO-I</TD><TD class=xl63 align=right>20110701</TD><TD align=right>912.4</TD><TD align=right>912.4</TD><TD align=right>899</TD><TD align=right>43</TD><TD align=right>1003500</TD><TD align=right>21500</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ABIRLANUVO-II</TD><TD class=xl63 align=right>20110701</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>1500</TD><TD align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ABIRLANUVO-III</TD><TD class=xl63 align=right>20110701</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ACC-I</TD><TD class=xl63 align=right>20110701</TD><TD align=right>950.3</TD><TD align=right>964.5</TD><TD align=right>939.75</TD><TD align=right>2647</TD><TD align=right>1030250</TD><TD align=center>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ACC-II</TD><TD class=xl63 align=right>20110701</TD><TD align=right>950.1</TD><TD align=right>961.3</TD><TD align=right>938.45</TD><TD align=right>202</TD><TD align=right>82750</TD><TD align=center>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ACC-III</TD><TD class=xl63 align=right>20110701</TD><TD align=right>945.95</TD><TD align=right>945.95</TD><TD align=right>938.5</TD><TD align=right>59</TD><TD align=right>14750</TD><TD align=center>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ADANIENT-I</TD><TD class=xl63 align=right>20110701</TD><TD align=right>722</TD><TD align=right>722</TD><TD align=right>696.05</TD><TD align=right>380</TD><TD align=right>1062000</TD><TD align=center>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ADANIENT-II</TD><TD class=xl63 align=right>20110701</TD><TD align=right>704.7</TD><TD align=right>704.7</TD><TD align=right>704.7</TD><TD align=right>1</TD><TD align=right>500</TD><TD align=center>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ADANIENT-III</TD><TD class=xl63 align=right>20110701</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=right>0</TD><TD align=center>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ADANIPOWER-I</TD><TD class=xl63 align=right>20110701</TD><TD align=right>111.05</TD><TD align=right>111.05</TD><TD align=right>110.2</TD><TD align=right>184</TD><TD align=right>4726000</TD><TD align=center>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl64 height=20>ADANIPOWER-II</TD><TD class=xl63 align=right>20110701</TD><TD align=right>110.85</TD><TD align=right>110.85</TD><TD align=right>110.85</TD><TD align=right>1</TD><TD align=right>4000</TD><TD align=center>#N/A</TD></TR></TBODY></TABLE>
Click to expand...
Does this data come from a web page?
 
Upvote 0
Yes sir in .csv format. I copied the data from Lot size into Sheet2 of Data

Both the files are in .Csv format.

Regards,

Zaska
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
364
dilshod_k
D

Forum statistics

Threads
1,224,525
Messages
6,179,319
Members
452,905
Latest member
deadwings

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top