Search & Find String of Words

[flds]

Hello,
I am looking to create a formula.
My requirement is, I have over 1200 rows of data and filling up. I am trying to match text. I have text in column A & B. I need the same string of words contained in Col 'B', to match in Col 'A'. (String of words in Col 'A' could be long in some rows). Return "Yes" or "No" in Col 'C', on same row as match found in Col 'A'.
Hope I am clear in my requirement.

I googled and tried these formulas, and failed

"IFERROR(IF(MATCHif("*"&B1&"*",A:A,0),"yes",),"no")"

"IF(COUNTIF(A:A, "*"&$B$1&"*"), "Yes", "")"

Your help would be greatly appreciated. Thanks

 

[Cubist]

Some sample data and which Excel version you're using would be helpful.

 

[kvsrinivasamurthy]

In C1. ARRAY formula needsCtrl+Shift+Enter. Not simple entry.

=IF(B1="","",IF(SUM(1*(ISNUMBER(FIND(B1,$A$2:$A$4)))),"YES","NO"))

 

[flds]

Hello Cubist,
Thanks for your quick response, much appreciate.
I am using Excel Version 2021. To send you data sample I will have to learn how to do that.

It's very simple,
In column 'A' I have long sentences or paragraph in them are embedded the names of staff with their first name and last name, some with first, middle and last names.
I need to search these staff names from column 'B', find on which row in column 'A' they are in.
In Column 'C' with a help of a formula return a 'Yes' on the row the name is found.
Once found I sort column 'C' with 'Yes' and create a report.
I hope this is clear. Let me know if you still need a data sample.
Thanks

 

[Cubist]

See if this works for you (without the need of the helper column C).

Book1
	A	B	C	D
1	Names	Search Name		Report
2	John Michael Smith	John		John Michael Smith
3	Smith, John Michael			Smith, John Michael
4	Emily Rose Carter			David L. Johnson
5	Carter, Emily Rose			Johnson, David L.
6	David L. Johnson
7	Johnson, David L.
8	Sophia Anne Thompson
9	Thompson, Sophia Anne
10	James O'Connor
11	O'Connor, James
12	Robert K. Williams
13	Williams, Robert K.
14	Anna-Marie Lee
15	Lee, Anna-Marie
16	Benjamin G. Martinez
17	Martinez, Benjamin G.
18	Victoria F. Cruz
19	Cruz, Victoria F.
20	Nathaniel Jameson Brown
21	Brown, Nathaniel Jameson
22	Olivia Hope Grant
23	Grant, Olivia Hope
Sheet5

Cell Formulas
Range	Formula
D2:D5	D2	=FILTER(A2:A23,ISNUMBER(SEARCH(B2,A2:A23)),"")
Dynamic array formulas.

 

[flds]

In C1. ARRAY formula needsCtrl+Shift+Enter. Not simple entry.

=IF(B1="","",IF(SUM(1*(ISNUMBER(FIND(B1,$A$2:$A$4)))),"YES","NO"))

Thanks kvsrinivasamurthy, for your response and formula.
It looks like the formula works also without Ctrl+Shift+Enter. This is not what I am looking for. I would appreciate if you could modify the formula as I have described below.

In Column 'C', I am looking for the 'Yes' to return on the same row on which the content of 'B' is found in column.'A'.
I hope this makes sense of my requirement.
Thanks

 

[Peter_SSs]

I am using Excel Version 2021

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if one of these does what you want, noting especially row 16. Your initial description talked about "words" but your attempted formulas would have found "strings" not necessarily "words" so I'm not quite sure what you are after. (All the more reason for providing sample data and results as requested by @Cubist )
To provide sample data (& expected results) I suggest that you investigate XL2BB.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Try these in row 2 (don't copy down the columns)
Adjust the column A range for your larger data.

The "word match" formula may not be very reliable if the column A data contains punctuation. If that is the case and you need more help, then varied sample data and the corresponding results become even more useful.

24 11 20.xlsm
	A	B	C	D
1	Names	John	Text match	Word match
2	John Michael Smith		Yes	Yes
3	Smith, John Michael		Yes	Yes
4	Emily Rose Carter		No	No
5	Carter, Emily Rose		No	No
6	David L. Johnson		Yes	No
7	Johnson, David L.		Yes	No
8			No	No
9	Thompson, Sophia Anne		No	No
10	James O'Connor		No	No
11	O'Connor, James		No	No
12	Robert K. Williams		No	No
13			No	No
14			No	No
15			No	No
16	Tom Micheljohn		Yes	No
17	Martinez, Benjamin G.		No	No
18	Victoria F. Cruz		No	No
19	Cruz, Victoria F.		No	No
20	Nathaniel Jameson Brown		No	No
21	Brown, Nathaniel Jameson		No	No
22	Olivia Hope Grant		No	No
23	Grant, Olivia Hope		No	No
Match text

Cell Formulas
Range	Formula
C2:C23	C2	=IF(ISNUMBER(SEARCH(B1,A2:A23)),"Yes","No")
D2:D23	D2	=IF(ISNUMBER(SEARCH(" "&B1&" "," "&A2:A23&" ")),"Yes","No")
Dynamic array formulas.

 

[kvsrinivasamurthy]

Try. In C1
=IF($B1="","",IF(1*(ISNUMBER(FIND($B1,$A$1:$A$4))),"YES","NO"))

 

[flds]

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if one of these does what you want, noting especially row 16. Your initial description talked about "words" but your attempted formulas would have found "strings" not necessarily "words" so I'm not quite sure what you are after. (All the more reason for providing sample data and results as requested by @Cubist )
To provide sample data (& expected results) I suggest that you investigate XL2BB.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Try these in row 2 (don't copy down the columns)
Adjust the column A range for your larger data.

The "word match" formula may not be very reliable if the column A data contains punctuation. If that is the case and you need more help, then varied sample data and the corresponding results become even more useful.

24 11 20.xlsm
	A	B	C	D
1	Names	John	Text match	Word match
2	John Michael Smith		Yes	Yes
3	Smith, John Michael		Yes	Yes
4	Emily Rose Carter		No	No
5	Carter, Emily Rose		No	No
6	David L. Johnson		Yes	No
7	Johnson, David L.		Yes	No
8			No	No
9	Thompson, Sophia Anne		No	No
10	James O'Connor		No	No
11	O'Connor, James		No	No
12	Robert K. Williams		No	No
13			No	No
14			No	No
15			No	No
16	Tom Micheljohn		Yes	No
17	Martinez, Benjamin G.		No	No
18	Victoria F. Cruz		No	No
19	Cruz, Victoria F.		No	No
20	Nathaniel Jameson Brown		No	No
21	Brown, Nathaniel Jameson		No	No
22	Olivia Hope Grant		No	No
23	Grant, Olivia Hope		No	No
Match text

Cell Formulas
Range	Formula
C2:C23	C2	=IF(ISNUMBER(SEARCH(B1,A2:A23)),"Yes","No")
D2:D23	D2	=IF(ISNUMBER(SEARCH(" "&B1&" "," "&A2:A23&" ")),"Yes","No")
Dynamic array formulas.

[/RANGE

Cell Formulas
Range	Formula
Dynamic array formulas.

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if one of these does what you want, noting especially row 16. Your initial description talked about "words" but your attempted formulas would have found "strings" not necessarily "words" so I'm not quite sure what you are after. (All the more reason for providing sample data and results as requested by @Cubist )
To provide sample data (& expected results) I suggest that you investigate XL2BB.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Try these in row 2 (don't copy down the columns)
Adjust the column A range for your larger data.

The "word match" formula may not be very reliable if the column A data contains punctuation. If that is the case and you need more help, then varied sample data and the corresponding results become even more useful.

24 11 20.xlsm
	A	B	C	D
1	Names	John	Text match	Word match
2	John Michael Smith		Yes	Yes
3	Smith, John Michael		Yes	Yes
4	Emily Rose Carter		No	No
5	Carter, Emily Rose		No	No
6	David L. Johnson		Yes	No
7	Johnson, David L.		Yes	No
8			No	No
9	Thompson, Sophia Anne		No	No
10	James O'Connor		No	No
11	O'Connor, James		No	No
12	Robert K. Williams		No	No
13			No	No
14			No	No
15			No	No
16	Tom Micheljohn		Yes	No
17	Martinez, Benjamin G.		No	No
18	Victoria F. Cruz		No	No
19	Cruz, Victoria F.		No	No
20	Nathaniel Jameson Brown		No	No
21	Brown, Nathaniel Jameson		No	No
22	Olivia Hope Grant		No	No
23	Grant, Olivia Hope		No	No
Match text

Cell Formulas
Range	Formula
C2:C23	C2	=IF(ISNUMBER(SEARCH(B1,A2:A23)),"Yes","No")
D2:D23	D2	=IF(ISNUMBER(SEARCH(" "&B1&" "," "&A2:A23&" ")),"Yes","No")
Dynamic array formulas.

Hello Peter Thank you so much for your time. I will be using both your formulas, which are helpful.
I modified the D2 formula =IF(ISNUMBER(SEARCH(" "&B1&" "," "&A2:A23&" ")),"Yes","No") I added the asterisk "*"&B1&"*". This gives me the search for multiple words. Thank you once again.

IF(ISNUMBER(SEARCH(" "&B1&" "," "&A2:A23&" ")),"Yes","No")

IF(ISNUMBER(SEARCH(B1,A2:A23)),"Yes","No")

IF(ISNUMBER(SEARCH(" "&B1&" "," "&A2:A23&" ")),"Yes","No")

 

[flds]

Try. In C1
=IF($B1="","",IF(1*(ISNUMBER(FIND($B1,$A$1:$A$4))),"YES","NO"))

Mr Murthy, Thank you for your formula, Sorry the second formula was not helpful, however I will be using your formula along with Peters 2 formulas. Much appreciate for your time to help me.