If text contains the Vlookup

M

Mebs

Board Regular
Joined
Mar 16, 2009
Messages
51
I am trying to do a price comparison between a list price and what I have actually being charged. The problem is that the list Pirce doesn't have any part numbers only description. So I have to search the text and match the description and then carry our a vlookup on that text to find the correct price.

<TABLE style="WIDTH: 389pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=519 border=0 x:str><COLGROUP><COL style="WIDTH: 245pt; mso-width-source: userset; mso-width-alt: 11958" width=327><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 245pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=327 height=17>Price List</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Description</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Price</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">UOM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>25 X 50 MM Whitewood Off Saw Treated Tile Batten</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>100</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">M</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="38 X 50 MM Whitewood Off Saw Treated ">38 X 50 MM Whitewood Off Saw Treated </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>100</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">M</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>47 X 50 MM Whitewood Off Saw Treated</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>100</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">M</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>47 X 75 MM Whitewood Off Saw Treated</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>100</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">M</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="47 X 100 MM Whitewood Off Saw Treated ">47 X 100 MM Whitewood Off Saw Treated </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>5</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>100</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">M</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12 X 70 MM Redwood R2A Facing</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>14</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>100</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">M</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12 X 95 MM Redwood R1A Skirting</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>15</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>100</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">M</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5.5 MM X 2440 X 1220 FAR Eastern Ply WBP BB/CC</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>16</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sheet</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>9 MM X 2440 X 1220 FAR Eastern Ply WBP BB/CC</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>17</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sheet</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12 MM X 2440 X 1220 FAR Eastern Ply WBP BB/CC</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>18</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sheet</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>18 MM X 2440 X 1220 FAR Eastern Ply WBP BB/CC</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>19</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sheet</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="2440 X 1220 X 18 MM MDF ">2440 X 1220 X 18 MM MDF </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>20</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sheet</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="2440 X 1220 X 18 MM M/R MDF ">2440 X 1220 X 18 MM M/R MDF </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>21</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sheet



</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 300pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=400 border=0 x:str><COLGROUP><COL style="WIDTH: 261pt; mso-width-source: userset; mso-width-alt: 12726" width=348><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD id=td_post_1885590 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 261pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=348 height=17>List of what I bought</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=52></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>LINE DESCRIPTION</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">QTY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2440X1220X18MM M/R MDF</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2440X1220X12MM MDF</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12X70MM REDWOOD R2A FACING</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>604</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12X45MM REDWOOD DAR</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>51</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>45X45MM WHITEWOOD REG&TREATED</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>201</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2040X926 44MM FLUSH SAPELE DOOR</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>15X70MM PRIMED MDF R2A</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>102.6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>33X120MM REDWOOD DAR</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>52.2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2440X1220X12MM M/R MDF</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>47X75MM WHITEWOOD O/S</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>126</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2440X1220X12MM MDF</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12x95mm AMERICAN WHITE OAK SKIRTING</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>55</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>18MM X2440X1220 FAR EASTERN PLYWOOD</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2440X1220X18MM MDF</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>47X50MM WHITEWOOD O/S TREATED</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>144</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>25X50MM WHITEWOOD O/S TREATED TILE BATTE</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>240</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12X38MM WHITEWOOD O/S TREATED COUNTER B</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>120</TD></TR></TBODY></TABLE>

So I need to search cell a1 for the desctiption and lookin the price list for the price; eg Bought '2440X1220X18MM M/R MDF' and the list price say I should have paid £21 per sheet. Descriptions not always consistant.

Thanks
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Erm the descriptions dont match.
Your Price List has spaces in the description
Your Line Description has those spaces removed

VLOOKUP's not gonna work
 
Upvote 0
Re: If text contains then Vlookup

Yes, I know. The Price list is something typed up but the things I bought are transmitted at the point of sale and then sent in CSV.

Any suggestions? Could always loose the spaces in the Price List.
 
Upvote 0
Re: If text contains then Vlookup

Maybe a combination of UPPER and SUBSTITUTE (change spaces to null "")
VLOOKUP might work then
 
Upvote 0
Re: If text contains then Vlookup

Thanks Special-K99 but what would the formula look like?
 
Upvote 0
Re: If text contains then Vlookup

Something along lines of
=VLOOKUP(UPPER(SUBSTITUTE(A1," ","")),B1:C100,2)

where A1 is lookup code
B1:C100 is the table

The above would return the data in column 2 of the table
 
Upvote 0
You might want to try this.
Cell H2 contains the CSE formula
=VLOOKUP(SUBSTITUTE(F2," ",""),SUBSTITUTE($A$1:$C$14," ",""),2,FALSE)+0
which is entered with Ctrl-Shift-Enter (Cmd+Return for Mac) and dragged down.

Note that there are many bought items that are not on the list.
e.g. the list item 2440X1220X12MM MDF in G4 has no matching description in column A (which lists only 18MM MDF)

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=325><b>A</b><td width=25><b>B</b><td width=55><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=275><b>F</b><td width=25><b>G</b><td width=25><b>H</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>Description<td align="left" bgcolor=#FFFFFF>Price<td align="left" bgcolor=#FFFFFF>UOM<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Line Description<td align="left" bgcolor=#FFFFFF>Qty<td align="left" bgcolor=#FFFFFF>Price</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>25 X 50 MM Whitewood Off Saw Treated Tile Batten<td align="right" bgcolor=#FFFFFF>1<td align="left" bgcolor=#FFFFFF>100 M<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>2440X1220X18MM M/R MDF<td align="right" bgcolor=#FFFFFF>4<td align="right" bgcolor=#FFFFFF>21</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>38 X 50 MM Whitewood Off Saw Treated<td align="right" bgcolor=#FFFFFF>2<td align="left" bgcolor=#FFFFFF>100 M<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>2440X1220X12MM MDF<td align="right" bgcolor=#FFFFFF>6<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF>47 X 50 MM Whitewood Off Saw Treated<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF>100 M<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>12X70MM REDWOOD R2A FACING<td align="right" bgcolor=#FFFFFF>604<td align="right" bgcolor=#FFFFFF>14</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF>47 X 75 MM Whitewood Off Saw Treated<td align="right" bgcolor=#FFFFFF>4<td align="left" bgcolor=#FFFFFF>100 M<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>12X45MM REDWOOD DAR<td align="right" bgcolor=#FFFFFF>51<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF>47 X 100 MM Whitewood Off Saw Treated<td align="right" bgcolor=#FFFFFF>5<td align="left" bgcolor=#FFFFFF>100 M<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>45X45MM WHITEWOOD REG&TREATED<td align="right" bgcolor=#FFFFFF>201<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="left" bgcolor=#FFFFFF>12 X 70 MM Redwood R2A Facing<td align="right" bgcolor=#FFFFFF>14<td align="left" bgcolor=#FFFFFF>100 M<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>2040X926 44MM FLUSH SAPELE DOOR<td align="right" bgcolor=#FFFFFF>2<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>8</b><td align="left" bgcolor=#FFFFFF>12 X 95 MM Redwood R1A Skirting<td align="right" bgcolor=#FFFFFF>15<td align="left" bgcolor=#FFFFFF>100 M<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>15X70MM PRIMED MDF R2A<td align="right" bgcolor=#FFFFFF>102.6<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>9</b><td align="left" bgcolor=#FFFFFF>5.5 MM X 2440 X 1220 FAR Eastern Ply WBP BB/CC<td align="right" bgcolor=#FFFFFF>16<td align="left" bgcolor=#FFFFFF>1 Sheet<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>33X120MM REDWOOD DAR<td align="right" bgcolor=#FFFFFF>52.2<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>10</b><td align="left" bgcolor=#FFFFFF>9 MM X 2440 X 1220 FAR Eastern Ply WBP BB/CC<td align="right" bgcolor=#FFFFFF>17<td align="left" bgcolor=#FFFFFF>1 Sheet<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>2440X1220X12MM M/R MDF<td align="right" bgcolor=#FFFFFF>15<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>11</b><td align="left" bgcolor=#FFFFFF>12 MM X 2440 X 1220 FAR Eastern Ply WBP BB/CC<td align="right" bgcolor=#FFFFFF>18<td align="left" bgcolor=#FFFFFF>1 Sheet<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>47X75MM WHITEWOOD O/S<td align="right" bgcolor=#FFFFFF>126<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>12</b><td align="left" bgcolor=#FFFFFF>18 MM X 2440 X 1220 FAR Eastern Ply WBP BB/CC<td align="right" bgcolor=#FFFFFF>19<td align="left" bgcolor=#FFFFFF>1 Sheet<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>2440X1220X12MM MDF<td align="right" bgcolor=#FFFFFF>2<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>13</b><td align="left" bgcolor=#FFFFFF>2440 X 1220 X 18 MM MDF<td align="right" bgcolor=#FFFFFF>20<td align="left" bgcolor=#FFFFFF>1 Sheet<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>12x95mm AMERICAN WHITE OAK SKIRTING<td align="right" bgcolor=#FFFFFF>55<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>14</b><td align="left" bgcolor=#FFFFFF>2440 X 1220 X 18 MM M/R MDF<td align="right" bgcolor=#FFFFFF>21<td align="left" bgcolor=#FFFFFF>1 Sheet<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>18MM X2440X1220 FAR EASTERN PLYWOOD<td align="right" bgcolor=#FFFFFF>2<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>15</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>2440X1220X18MM MDF<td align="right" bgcolor=#FFFFFF>1<td align="right" bgcolor=#FFFFFF>20</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>16</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>47X50MM WHITEWOOD O/S TREATED<td align="right" bgcolor=#FFFFFF>144<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>17</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>25X50MM WHITEWOOD O/S TREATED TILE BATTE<td align="right" bgcolor=#FFFFFF>240<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>18</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>12X38MM WHITEWOOD O/S TREATED COUNTER B<td align="right" bgcolor=#FFFFFF>120<td align="left" bgcolor=#FFFFFF>#N/A</tr>
</table>
 
Upvote 0
@mikerickson, works like a charm. Your correct that not all items bought areon the price list and that for our Buyer to resolve. Thanks.

@special-K99, thanks, too.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
854
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
Adding a custom message.
Replies
1
Views
447
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
172
dilshod_k
D

Forum statistics

Threads
1,215,514
Messages
6,125,272
Members
449,219
Latest member
daynle

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