Looking for keywords that matches exactly in a set of strings

[jt9000]

I'm trying to search for an exact match of array of strings in each individual cell that contains long strings.
The strings could be comma delimited, which is considered acceptable, but I'm having trouble finding exact word match when a word contains the alphabets of another word. E.g. "Clamp" should not evaluate true when searching for "Lamp". Please advise how I can achieve this, below is what I have so far:

Book1.xlsx
	A	B	C	D
1	String	Evaluated State	Actual State	Keyword
2	Lamp	TRUE	TRUE	Lamp
3	Clamp	TRUE	FALSE	Right
4	aagsdg	FALSE	FALSE	Left
5	asdfs	FALSE	FALSE
6	Leftie	TRUE	FALSE
7	Left	TRUE	TRUE
8	Lamp, Bright	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=OR(ISNUMBER(SEARCH($D$2:$D$4,A2)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[johnnyL]

Search in not case sensitive, use the Find function instead.

 

[jt9000]

Search in not case sensitive, use the Find function instead.

Case sensitivity is not crucial in my case. I'm more concerned about the keyword strings match the word is the search string exactly. E.g. When searching for "Lamp", "Clamp should not evaluate to TRUE, even though it contains the strings "lamp" after the "c". Same thing with "Leftie" shouldn't evaluate to true when searching for "Left". But "Lamp, Bright" should evaluate to TRUE when searching for "Lamp", since it matches the word "Lamp" exactly, even though it contains another word in the string.

 

[jtakw]

Hi,

Does this work for you:

Book3.xlsx
	A	B	C
1	String	Result	Keyword
2	Lamp	TRUE	Lamp
3	Clamp	FALSE	Right
4	aagsdg	FALSE	Left
5	asdfs	FALSE
6	Leftie	FALSE
7	Left	TRUE
8	Lamp, Bright	TRUE
Sheet1005

Cell Formulas
Range	Formula
B2:B8	B2	=ISNUMBER(LOOKUP(2,1/SEARCH(", "&$C$2:C$4&", ",", "&TRIM(A2)&", ")))

 

[jt9000]

Hi,

Does this work for you:

Book3.xlsx
	A	B	C
1	String	Result	Keyword
2	Lamp	TRUE	Lamp
3	Clamp	FALSE	Right
4	aagsdg	FALSE	Left
5	asdfs	FALSE
6	Leftie	FALSE
7	Left	TRUE
8	Lamp, Bright	TRUE
Sheet1005

Cell Formulas
Range	Formula
B2:B8	B2	=ISNUMBER(LOOKUP(2,1/SEARCH(", "&$C$2:C$4&", ",", "&TRIM(A2)&", ")))

Yes, that's exactly what I needed. Thank you so much!
Would you mind to explain how it works? I'm a bit perplexed how it allowed Clamp to not evaluate true due to lamp being part of the word
Thanks.

 

[jtakw]

Yes, that's exactly what I needed. Thank you so much!
Would you mind to explain how it works? I'm a bit perplexed how it allowed Clamp to not evaluate true due to lamp being part of the word
Thanks.

Within the formula, we isolated the Text with ", " ( comma & space ), so therefore:

", clamp, " does not equal to ", lamp, "

Also, I noticed in your posted sample, you have a leading Space in A2, that's why I also used TRIM

 

[bebo021999]

Would you mind to explain how it works?

Add special letter (, or @ or |,...) to separate whole words. See how it works:
",Lamp," with ",Clamp," => FALSE
",Lamp," with ",Lamp, Bright," => TRUE
",Lamp," with ",Clamp, Bright," => FALSE

 

[Peter_SSs]

If the suggested formula works for you then this slightly simpler one with less calculation required should also - column D below.

However, I am wondering about this statement.

cell that contains long strings.
The strings could be comma delimited,

If the cells contain long strings is every single word in the cell separated by comma space? If not then both column C & D formulas may return incorrect results - see row 9 below.

If it is possible that some words are separated by comma space and some just by space, then you may need to consider a formula more like column E.

22 02 15.xlsm
	A	B	C	D	E
1	String	Keyword	Post #4	Option 1	Option 2
2	Lamp	Lamp	TRUE	TRUE	TRUE
3	Clamp	Right	FALSE	FALSE	FALSE
4	aagsdg	Left	FALSE	FALSE	FALSE
5	asdfs		FALSE	FALSE	FALSE
6	Leftie		FALSE	FALSE	FALSE
7	Left		TRUE	TRUE	TRUE
8	Lamp, Bright		TRUE	TRUE	TRUE
9	Clamp, Right and Left		FALSE	FALSE	TRUE
Keywords

Cell Formulas
Range	Formula
C2:C9	C2	=ISNUMBER(LOOKUP(2,1/SEARCH(", "&$B$2:B$4&", ",", "&TRIM(A2)&", ")))
D2:D9	D2	=COUNT(SEARCH(", "&$B$2:B$4&", ",", "&TRIM(A2)&", "))>0
E2:E9	E2	=COUNT(SEARCH(" "&$B$2:B$4&" "," "&SUBSTITUTE(A2,","," ")&" "))>0

 

[jt9000]

If the suggested formula works for you then this slightly simpler one with less calculation required should also - column D below.

However, I am wondering about this statement.

If the cells contain long strings is every single word in the cell separated by comma space? If not then both column C & D formulas may return incorrect results - see row 9 below.

If it is possible that some words are separated by comma space and some just by space, then you may need to consider a formula more like column E.

22 02 15.xlsm
	A	B	C	D	E
1	String	Keyword	Post #4	Option 1	Option 2
2	Lamp	Lamp	TRUE	TRUE	TRUE
3	Clamp	Right	FALSE	FALSE	FALSE
4	aagsdg	Left	FALSE	FALSE	FALSE
5	asdfs		FALSE	FALSE	FALSE
6	Leftie		FALSE	FALSE	FALSE
7	Left		TRUE	TRUE	TRUE
8	Lamp, Bright		TRUE	TRUE	TRUE
9	Clamp, Right and Left		FALSE	FALSE	TRUE
Keywords

Cell Formulas
Range	Formula
C2:C9	C2	=ISNUMBER(LOOKUP(2,1/SEARCH(", "&$B$2:B$4&", ",", "&TRIM(A2)&", ")))
D2:D9	D2	=COUNT(SEARCH(", "&$B$2:B$4&", ",", "&TRIM(A2)&", "))>0
E2:E9	E2	=COUNT(SEARCH(" "&$B$2:B$4&" "," "&SUBSTITUTE(A2,","," ")&" "))>0

Thank you! That was exactly what I needed. I really appreciate everyone's help here

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.