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 :)
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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*")
 
Upvote 0
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'



Excel 2010
ABC
1Oppty DescriptionOK FieldsMatch??
2(SIP)NEW EA AYUNTAMIENTO DE VIGOCustomer has been notified that partner may receive incentives for sellinggovernement disclosure completeNo
3Leder IKT og Informasjonsforvaltning, Goverment disclosure notifiedgovernment disclosure compleetYes
4Acquisto della licenze Windows 7 per i nuovi PCCustomer has been adviseNo
5can confirm we have told our public sector customer we might be receiving an incentive payment for this deal.Goverment desclosure notifiedNo
6IT-Manager, [email]aghe@petttt.com[/email]Customer is awareNo
7Alessandro [email]simeone@piii.ch[/email]Customer has been notifiedNo
8(700 admin, 2300 elever/skola) caset r enkelt, de kr Zenworks som r licenserat hela 2012 men med lite sponsring s kan vi f dem att trilla ver till SCCM i brjan p 2012.Goverment disclosure notifiedNo
9Arne kerblom, Sundsvall//PS disclosure completedNo
10Rdgiver Team IT / Goverment disclosure notifiedCustomer disclosure completeYes
11other licensing opportunityNo
12JumpstartNo
13Baxley Miceli, MalmNo
14Der Kunde wurde darber informiert, dass eventuell ein Solution Incentive ausgezahlt wird.No
15BI-lsning.No
16Tienen SQL 2008 en tres nodos para tema radiofonico. EnterpriseNo
17IKT konsulent, Goverment Disclosure NotifiedYes
18Consulting for scoping the project, Proof-of-concept Lync voice environmentNo
19title: teamleder |Goverment disclosure notified.Yes
20Lync lsning lisenserNo
21Title: nestleder, Goverment disclosure notified.Yes
22Position: CIO, email. Goverment Disclosure NotifiedYes
23Customer disclosure completedYes
24Customer has been has been notified d of the incentive program / PS disclosure completedYes
Sheet1



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.


Excel 2010
ABCD
1Oppty DescriptionOK FieldsYesResult
2(SIP)NEW EA AYUNTAMIENTO DE VIGOCustomer has been notified that partner may receive incentives for sellinggovernement disclosure completeYesYes
3Leder IKT og Informasjonsforvaltning, Goverment disclosure notifiedgovernment disclosure compleetYesYes
4Acquisto della licenze Windows 7 per i nuovi PCCustomer has been adviseYes#N/A
5can confirm we have told our public sector customer we might be receiving an incentive payment for this deal.Goverment desclosure notifiedYes#N/A
6IT-Manager, [email]aghe@petttt.com[/email]Customer is awareYes#N/A
7Alessandro [email]simeone@piii.ch[/email]Customer has been notifiedYes#N/A
8(700 admin, 2300 elever/skola) caset r enkelt, de kr Zenworks som r licenserat hela 2012 men med lite sponsring s kan vi f dem att trilla ver till SCCM i brjan p 2012.Goverment disclosure notifiedYes#N/A
9Arne kerblom, Sundsvall//PS disclosure completedYes#N/A
10Rdgiver Team IT / Goverment disclosure notifiedCustomer disclosure completeYesYes
11other licensing opportunity#N/A
12Jumpstart#N/A
13Baxley Miceli, Malm#N/A
14Der Kunde wurde darber informiert, dass eventuell ein Solution Incentive ausgezahlt wird.#N/A
15BI-lsning.#N/A
16Tienen SQL 2008 en tres nodos para tema radiofonico. Enterprise#N/A
17IKT konsulent, Goverment Disclosure NotifiedYes
18Consulting for scoping the project, Proof-of-concept Lync voice environment#N/A
19title: teamleder |Goverment disclosure notified.Yes
20Lync lsning lisenser#N/A
21Title: nestleder, Goverment disclosure notified.Yes
22Position: CIO, email. Goverment Disclosure NotifiedYes
23Customer disclosure completedYes
24Customer has been has been notified d of the incentive program / PS disclosure completedYes
Sheet1
Cell Formulas
RangeFormula
D2=LOOKUP(2^15,SEARCH($B$2:$B$10,A2),$C$2:$C$10)


Appreciate your help :)
 
Upvote 0
=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!
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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