Compare cell contents with the given rnage of contents

[sbabu16]

Hi,

I have attached a workbook stating my problem. Any help is greatly appreciated.

Thanks.
SB16.

[benishiryo]

hi Babu. assuming the number of words is separated by a comma like you illustrated, then maybe:
=SUMPRODUCT(--(ISNUMBER(SEARCH($E$2:$E$4,A2))))=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

the above checks how many matches are there in column A in column E. then compares whether it's equals to the number of words in column A. to make it into Yes & No, try:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH($E$2:$E$4,A2))))=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1,"yes","no")

have a happy new year~

[rishikrsaw]

pls see it is helping.....
=IF(ISERROR(VLOOKUP(LEFT(A2,3),$E$2:$E$4,1,0)),"no","yes")

[sbabu16]

Thanks Benishiryo, it works well...

Thanks Rishikrsaw.

Happy new year.

[Indi_Ra]

pls see it is helping.....
=IF(ISERROR(VLOOKUP(LEFT(A2,3),$E$2:$E$4,1,0)),"no","yes")

Your formula gives a wrong answer if in a cell is something like aaa,zzz
Correct answer will be no

[rishikrsaw]

@Indi_Ra


it is working till the cell value is aaa,zzz
if cell value is zzz,aaa then the formula will give no ....

[Indi_Ra]

@Indi_Ra


it is working till the cell value is aaa,zzz
if cell value is zzz,aaa then the formula will give no ....

What do you mean formula is good till....????
Formula should work for all cases required by OP.
If formula working for all cases is OK else...

[rishikrsaw]

Hi

As per sbabu16 request (pls refer sbabu16's sheet column no b "Expected Result") all yes and column is fallowing only aaa bbb or ccc would come first in the cell as in if aaa.zzz then yes if eee.aaa then no ( pls see b5 sbabu16' comment).

and hence I can say formula is working....


do let me know if any thing is still Misconstrue .....

[Nithin563]

hi Babu. assuming the number of words is separated by a comma like you illustrated, then maybe:
=SUMPRODUCT(--(ISNUMBER(SEARCH($E$2:$E$4,A2))))=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

Happy New Year.......

[rishikrsaw]

I thing benishiryo already discuss this method by the way...........

[Indi_Ra]

Hi

As per sbabu16 request (pls refer sbabu16's sheet column no b "Expected Result") all yes and column is fallowing only aaa bbb or ccc would come first in the cell as in if aaa.zzz then yes if eee.aaa then no ( pls see b5 sbabu16' comment).

and hence I can say formula is working....


do let me know if any thing is still Misconstrue .....

In the comment "Babu, S (S.):
No, because it contains "eee" which is not in the database"

Answer is NO because cell CONTAINS "eee" , this mean "eee" could be, before or after other word.
I hope you finally understand.