vlookup with brackets

[sjbayer3]

I have a column of text that contains multi word strings, and some of the text has brackets in it. In adjacent columns there is numerical data I want to pull using vlookup.

For example:

<table style="width: 491px; height: 85px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:8192;width:168pt" width="224"> <col style="width:48pt" span="3" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:168pt" width="224" height="20">[accommodation for london]</td> <td style="width:48pt" width="64" align="right">2.87</td> <td style="width:48pt" width="64" align="right">0</td> <td style="width:48pt" width="64" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">[accommodation holiday]</td> <td align="right">2.87</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">[accommodation on beach]</td> <td align="right">2.87</td> <td align="right">0</td> <td align="right">0</td> </tr> </tbody></table>

I want to use vlookup, but its pulling the wrong text.

For example here are two columns, the first is the lookup_value used in vlookup, and the second column is the output with col_index of 1, in theory these should be the same:

<table style="width: 526px; height: 341px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5741;width:118pt" width="157"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">island accommodation</td> <td class="xl63" style="width:118pt" width="157">home vacation rentals</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">villa rental</td> <td class="xl63">cottage on the beach</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">villa for rental</td> <td class="xl63">cottage on the beach</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">accommodation for london</td> <td class="xl63">[accommodation on beach]</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">cottages lake</td> <td class="xl63">cootages 4 you</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">cottages on the lake</td> <td class="xl63">cootages 4 you</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">lake cottages</td> <td class="xl63">home vacation rentals</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">villas for holiday</td> <td class="xl63">cottage on the beach</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">holiday villas</td> <td class="xl63">cootages 4 you</td> </tr> </tbody></table>


Any suggestions?

the exact vlookup command i am using is as follows:
=VLOOKUP($A4,Sheet2!$A:$W,B$3)

 

[Aladin Akyurek]

You probably need...

=VLOOKUP("*"&$A4&"*",Sheet2!$A:$W,B$3,0)

 

[sjbayer3]

Aladin,

Thanks for that! I never knew you could use asterisk in excel the same way you can with searching.

The problem with this is as follows:

Some of the lookup_value's have brackets and some do not, using the asterisk will mix them as as below:

<table style="width: 479px; height: 84px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:6107;width:125pt" width="167"> <col style="mso-width-source:userset;mso-width-alt:5741;width:118pt" width="157"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:125pt" width="167" height="20">[island accommodation]</td> <td class="xl65" style="width:118pt" width="157">[island accommodation]</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">island accommodation</td> <td class="xl65">[island accommodation]</td> </tr> </tbody></table>So it finds the bracketed version for both the bracketed and non-bracketed lookup_value.

Thank you again!

 

[Aladin Akyurek]

Aladin,

Thanks for that! I never knew you could use asterisk in excel the same way you can with searching.

The problem with this is as follows:

Some of the lookup_value's have brackets and some do not, using the asterisk will mix them as as below:

<TABLE style="WIDTH: 479px; HEIGHT: 84px" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6107" width=167><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 125pt; HEIGHT: 15pt" height=20 width=167>[island accommodation]</TD><TD style="WIDTH: 118pt" class=xl65 width=157>[island accommodation]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>island accommodation</TD><TD class=xl65>[island accommodation]</TD></TR></TBODY></TABLE>So it finds the bracketed version for both the bracketed and non-bracketed lookup_value.

Thank you again!

You still need exact match...

=VLOOKUP($A4,Sheet2!$A:$W,B$3,0)

Does this solve your problem? If not, could you elaborate a bit more?

 

[sjbayer3]

Yeah problem solved. Thank you much!

 

[Aladin Akyurek]

Yeah problem solved. Thank you much!

You are welcome. Thanks for providing feedback.