Search for word in cell using vlookup

The_Rock

Board Regular
Joined
Jul 2, 2007
Messages
174
Hi Folks
Been racking my brains trying to find a solution for this.

In Column A, I have a bunch of Sentences which is basically a description field.

In order to meet criteria, somewhere in the sentence, it needs to state the following "Government Disclosure Complete". As I'm working with different countries across Europe, their spelling isn't always the same.
For example, I have created a list of acceptable terms which is:
governement disclosure complete
government disclosure compleet
Customer has been advise
Goverment desclosure notified
Customer is aware
Customer has been notified
Goverment disclosure notified
PS disclosure completed
Customer disclosure complete

I am trying to create a sort of wildcard vlookup that will search thru column A, if it can make a partial match to one of the above words, it will then return a simple yes or no answer.

Appreciate your help with this :)
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997
You might try something like this.

Code:
Function ISLIKE(arg, pattern As String) As Boolean
' Returns true if the first argument is like the second
ISLIKE = LCase(arg) Like pattern
End Function

then

=islike(B2,"govern*")
 

The_Rock

Board Regular
Joined
Jul 2, 2007
Messages
174
Hi Tinbendr
Thanks for your reply.
I'm not sure I can use that as the fields in the look up table vary.

I've managed to figure out the HTML maker so hopefully the below will make it clearer.

Column A is my Description Field.
Column B is the fields I want to look up (this will increase depending on the different variations).

What I'm trying to achieve is that Col A is looked up by Col B and result is given in Col C.
Column C is the result ie 'yes' or 'no'


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #DCE6F1;;">Oppty Description</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #DCE6F1;;">OK Fields</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #DCE6F1;;">Match??</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">(SIP)NEW EA AYUNTAMIENTO DE VIGOCustomer has been notified that partner may receive incentives for selling</td><td style="color: #0000FF;;">governement disclosure complete</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Leder IKT og Informasjonsforvaltning, Goverment disclosure notified</td><td style="color: #0000FF;;">government disclosure compleet</td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Acquisto della licenze Windows 7 per i nuovi PC</td><td style="color: #0000FF;;">Customer has been advise</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">can confirm we have told our public sector customer we might be receiving an incentive payment for this deal.</td><td style="color: #0000FF;;">Goverment desclosure notified</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">IT-Manager, aghe@petttt.com</td><td style="color: #0000FF;;">Customer is aware</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Alessandro simeone@piii.ch</td><td style="color: #0000FF;;">Customer has been notified</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">(700 admin, 2300 elever/skola) caset är enkelt, de kör Zenworks som är licenserat hela 2012 men med lite sponsring så kan vi få dem att trilla över till SCCM i början på 2012.</td><td style="color: #0000FF;;">Goverment disclosure notified</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Arne Åkerblom, Sundsvall//</td><td style="color: #0000FF;;">PS disclosure completed</td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Rådgiver – Team IT /  Goverment disclosure notified</td><td style="color: #0000FF;;">Customer disclosure complete</td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">other licensing opportunity</td><td style="text-align: right;;"></td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Jumpstart</td><td style="text-align: right;;"></td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Baxley Miceli, Malmö</td><td style="text-align: right;;"></td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Der Kunde wurde darüber informiert, dass eventuell ein Solution Incentive ausgezahlt wird.</td><td style="text-align: right;;"></td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">BI-lösning.</td><td style="text-align: right;;"></td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Tienen SQL 2008 en tres nodos para tema radiofonico. Enterprise</td><td style="text-align: right;;"></td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">IKT konsulent, Goverment Disclosure Notified</td><td style="text-align: right;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Consulting for scoping the project, Proof-of-concept Lync voice environment</td><td style="text-align: right;;"></td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">title: teamleder |Goverment disclosure notified.</td><td style="text-align: right;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Lync løsning lisenser</td><td style="text-align: right;;"></td><td style="text-align: center;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">Title: nestleder, Goverment disclosure notified.</td><td style="text-align: right;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Position: CIO, email. Goverment Disclosure Notified</td><td style="text-align: right;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Customer disclosure completed</td><td style="text-align: right;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Customer has been has been notified d of the incentive program / PS disclosure completed</td><td style="text-align: right;;"></td><td style="text-align: center;;">Yes</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />


Searching on the forum
(http://www.mrexcel.com/forum/showth...8-Challenge-of-the-Month&highlight=bill+jelen)
I was able to find the following code (#5 - genius solution!!) which I adapted. Can you guys help me to modify it so that if a match isn't made, it returns 'No' instead of #N/A.
I tried ISNA but did not succeed.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #DCE6F1;;">Oppty Description</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #DCE6F1;;">OK Fields</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #DCE6F1;;">Yes</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #DCE6F1;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">(SIP)NEW EA AYUNTAMIENTO DE VIGOCustomer has been notified that partner may receive incentives for selling</td><td style="color: #0000FF;;">governement disclosure complete</td><td style="text-align: center;;">Yes</td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Leder IKT og Informasjonsforvaltning, Goverment disclosure notified</td><td style="color: #0000FF;;">government disclosure compleet</td><td style="text-align: center;;">Yes</td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Acquisto della licenze Windows 7 per i nuovi PC</td><td style="color: #0000FF;;">Customer has been advise</td><td style="text-align: center;;">Yes</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">can confirm we have told our public sector customer we might be receiving an incentive payment for this deal.</td><td style="color: #0000FF;;">Goverment desclosure notified</td><td style="text-align: center;;">Yes</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">IT-Manager, aghe@petttt.com</td><td style="color: #0000FF;;">Customer is aware</td><td style="text-align: center;;">Yes</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Alessandro simeone@piii.ch</td><td style="color: #0000FF;;">Customer has been notified</td><td style="text-align: center;;">Yes</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">(700 admin, 2300 elever/skola) caset är enkelt, de kör Zenworks som är licenserat hela 2012 men med lite sponsring så kan vi få dem att trilla över till SCCM i början på 2012.</td><td style="color: #0000FF;;">Goverment disclosure notified</td><td style="text-align: center;;">Yes</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Arne Åkerblom, Sundsvall//</td><td style="color: #0000FF;;">PS disclosure completed</td><td style="text-align: center;;">Yes</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Rådgiver – Team IT /  Goverment disclosure notified</td><td style="color: #0000FF;;">Customer disclosure complete</td><td style="text-align: center;;">Yes</td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">other licensing opportunity</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Jumpstart</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Baxley Miceli, Malmö</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Der Kunde wurde darüber informiert, dass eventuell ein Solution Incentive ausgezahlt wird.</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">BI-lösning.</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Tienen SQL 2008 en tres nodos para tema radiofonico. Enterprise</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">IKT konsulent, Goverment Disclosure Notified</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Consulting for scoping the project, Proof-of-concept Lync voice environment</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">title: teamleder |Goverment disclosure notified.</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Lync løsning lisenser</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">Title: nestleder, Goverment disclosure notified.</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Position: CIO, email. Goverment Disclosure Notified</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Customer disclosure completed</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Customer has been has been notified d of the incentive program / PS disclosure completed</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Yes</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=LOOKUP(<font color="Blue">2^15,SEARCH(<font color="Red">$B$2:$B$10,A2</font>),$C$2:$C$10</font>)</td></tr></tbody></table></td></tr></table><br />

Appreciate your help :)
 

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997
=IF(ISNA(LOOKUP(2^15,SEARCH($A$2:$A$10,A3),$B$2:$B$10)),"No","Yes")
 

The_Rock

Board Regular
Joined
Jul 2, 2007
Messages
174
=IF(ISNA(LOOKUP(2^15,SEARCH($A$2:$A$10,A3),$B$2:$B$10)),"No","Yes")

Brilliant, thanks Tinbendr, just had to tweak the references but does what I need it to!
=IF(ISNA(LOOKUP(2^15,SEARCH($B$2:$B$10,A2),$B$2:$B$10)),"No","Yes")

Cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

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
Top