columns match

[jasman]

Hi I have two columns A and B. I need excel to return "true" in another column if the values in both match. However column A has some letters before the number I need to match. Is there any way of doing this: For examples
Column A =
<TABLE style="WIDTH: 103pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=137 border=0 x:str><COLGROUP><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 103pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: red" width=137 height=17>4R8Q-19A216-AC</TD></TR></TBODY></TABLE>

Column B= 19A216

I need thsi to return TRUE even though its a partial match#

thanks

 

[MrKowz]

Try switching up your formula to:

=ISNUMBER(MATCH("*-"&B2&"-*",supplierPartListSearch!$B$2:$B$19896,0))

This will force it to look for only the values that occur between the hyphens.

This way, it will return TRUE if you are searching for 14 in 1rt-14-AA
But it will return FALSE if you are searching for 14 in 1L2T-14K147-AA

 

[jasman]

ive chnaged the formual to this and it seems to work:

=ISNUMBER(MATCH("*-"&B1&"-*",supplierPartListSearch!$B$2:$B$19896,0))

 

[jasman]

Thank you

 

[jasman]

-

 

[MrKowz]

The formula is messy, but try:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #FFFFFF;;">29Y421</td><td style="background-color: #FFFFFF;;">19A216</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">TRUE</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FFFFFF;;">9N6J-12P412-AT</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">4R8Q-19A216-AC</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Not Ok because the number is Unknown</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet4</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=ISNUMBER(<font color="Blue">SUMPRODUCT(<font color="Red">--(<font color="Green">NOT(<font color="Purple">ISERROR(<font color="Teal">INDEX(<font color="#FF00FF">A:A,MATCH(<font color="Navy">"*-"&$B$1&"-*",A:A,0</font>)</font>)</font>)</font>)</font>),--(<font color="Green">ISNUMBER(<font color="Purple">SEARCH(<font color="Teal">"Not Ok",INDEX(<font color="#FF00FF">M:M,MATCH(<font color="Navy">"*-"&$B$1&"-*",A:A,0</font>)</font>)</font>)</font>)</font>),--(<font color="Green">ISNUMBER(<font color="Purple">SEARCH(<font color="Teal">"Unknown",INDEX(<font color="#FF00FF">M:M,MATCH(<font color="Navy">"*-"&$B$1&"-*",A:A,0</font>)</font>)</font>)</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />