find set of words in sentence

[trprasad78]

Hi

i have sent of words in sheet2 , sheet1 i have sentence.

In a sentence any word found it has to say YES or NO.

please suggest me.

Thank you

 

[Khalid NGO]

Hi Prasad, Good day and welcome to the forum.

Please try this in B2 and copy down:

=IF(ISERROR(LOOKUP(9^9,SEARCH('lookup Value'!$A$2:$A$6,A2))), "No","Yes")

Regards,

 

[Chihiro]

Another version.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH('lookup Value'!$A$1:$A$6,'RAW DATA'!A2)))>0,"YES","NO")

 

[trprasad78]

Hi Prasad, Good day and welcome to the forum.

Please try this in B2 and copy down:

=IF(ISERROR(LOOKUP(9^9,SEARCH('lookup Value'!$A$2:$A$6,A2))), "No","Yes")

Regards,

Thank you so much Mr. Khalid NGO,

Its working fine.

I never used lookup function. I would like to know what 9^9 why we used this.
i know vlookup and hlookup.

can you please explain why we need to use 9^9.

Thank you

 

[Ron Coderre]

Try this formula, copied down:
=IF(COUNT(INDEX(1/SEARCH('lookup Value'!$A$2:$A$6,A2),0)),"Yes","No")
Is that something you can work with?

 

[deciog]

trprasad78

My contribution, I have helped

Décio from Brazil

 

[Chihiro]

@trprasad78

Here's the explanation of Khalid's formula.
=IF(ISERROR(LOOKUP(9^9,SEARCH('lookup Value'!$A$2:$A$6,A2))), "No","Yes")

SEARCH(Find_String,Lookup_String) This portion returns numeric value if it finds Find_String within Lookup_String.

LOOKUP(9^9,SEARCH()), then exploits how LOOKUP function works.
LOOKUP finds greatest value in Range (SEARCH() in this case) without exceeding Lookup value (9^9).

Since 9^9 evaluates to 387,420,489. Due to Excel string length limitation, there is no possibility of numeric SEARCH() result exceeding that value and will resolve every time as long as Find_String is found.

Edit: Also it's important to note that SEARCH() by itself cannot deal with array of Find_String. It is for this reason Khalid nested it in LOOKUP and I used SUMPRODUCT(--ISNUMBER()).

 

[Khalid NGO]

Thanks @Chihiro

@Ron Coderre Sir welcome to chandoo.org, glad to see you here.

 

[Naresh Yadav]

Dear trprasd78

since your all the lookup value is the first instance of your Raw data

here is my contribution in thi scase..
Pls complete the formula with CTRL+SHIFT+ENTER

=IF(ISNUMBER(MATCH(1,SEARCH($D$1:$D$6,A2),0)),"yes","no")

regards
Naresh

 

[bosco_yip]

Another option,

1] B2, formula :

=COUNT(INDEX(SEARCH('lookup Value'!$A$2:$A$6,A2),))

2] B2 >> Custom Cell Format >> enter Type box:

[=1]"Yes";[=0]"No"

All copy down

Regards
Bosco

 

[deciog]

bosco yip

Thank you I learned more option

Decio

 

[trprasad78]

Thank you all

 

[ans511]

Guys , I am trying to do something similar.But going further can we know which value from the lookup it matched ?? instead of simple yes & No

 

[NARAYANK991]

Guys , I am trying to do something similar.But going further can we know which value from the lookup it matched ?? instead of simple yes & No

Hi ,

Something like this ?

Narayan

 

[shrivallabha]

There's one formula forensics article for such requirement:
https://chandoo.org/wp/2013/09/12/formula-forensics-no-34-find-a-list-of-words-in-a-cell/

That's the only one article I wrote

 

[ans511]

Hi ,

Something like this ?

Narayan

Thanks Narayan .. I want the other way . The value from lookup Value sheet should be populated in RAW DATA

 

[ans511]

There's one formula forensics article for such requirement:
https://chandoo.org/wp/2013/09/12/formula-forensics-no-34-find-a-list-of-words-in-a-cell/

That's the only one article I wrote

Thanks shrivallabha the article is awesome and would help me to the core...

 

[NARAYANK991]

Thanks Narayan .. I want the other way . The value from lookup Value sheet should be populated in RAW DATA

Hi ,

Will this do ?

=INDEX('lookup Value'!$A$1:$A$6, MATCH(TRUE, ISNUMBER(SEARCH('lookup Value'!$A$1:$A$6, A2)), 0))

Narayan