Formula to find any words from Col A in Col B (on same row) and confirm yes / no in Col C

[ellison]

Hi, I'm trying to find a formula to check whether any search terms in Col A (which would be separated by spaces), are contained in in Col B (words also separated by spaces), and put a "yes" or "no" in Col C
In the example below, Col A is the data to be cleaned up, Col B is the Categories to be searched, & Col C = "are any words in Col A contained in Col B".
I've tried all sorts of helper columns and FIND and SEARCHES, but I'm wondering if there is a better way with a single formula!?

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Col A: Data To be Cleaned
[/TD]
[TD]Col B: Categories
[/TD]
[TD]Any words from Col A contained in Col B?
[/TD]
[/TR]
[TR]
[TD]ball and socket
[/TD]
[TD]balls, joints, bearings
[/TD]
[TD]yes
[/TD]
[/TR]
[TR]
[TD]belt on car
[/TD]
[TD]belting
[/TD]
[TD]yes
[/TD]
[/TR]
[TR]
[TD]tube for valve
[/TD]
[TD]pipe and tube
[/TD]
[TD]yes
[/TD]
[/TR]
[TR]
[TD]regulators, valves
[/TD]
[TD]adapters
[/TD]
[TD]no
[/TD]
[/TR]
[TR]
[TD]air cond'g pipe
[/TD]
[TD]pipes and exhausts
[/TD]
[TD]yes
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Maybe:

	A	B	C	D
Yes
Yes
Yes
No
Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Col A: Data To be Cleaned[/TD]
[TD="bgcolor: #FAFAFA"]Col B: Categories[/TD]
[TD="bgcolor: #FAFAFA"]Any words from Col A contained in Col B?[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]ball and socket[/TD]
[TD="bgcolor: #FAFAFA"]balls, joints, bearings[/TD]
[TD="bgcolor: #FAFAFA"]yes[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]belt on car[/TD]
[TD="bgcolor: #FAFAFA"]belting[/TD]
[TD="bgcolor: #FAFAFA"]yes[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]tube for valve[/TD]
[TD="bgcolor: #FAFAFA"]pipe and tube[/TD]
[TD="bgcolor: #FAFAFA"]yes[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]regulators, valves[/TD]
[TD="bgcolor: #FAFAFA"]adapters[/TD]
[TD="bgcolor: #FAFAFA"]no[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]air cond'g pipe[/TD]
[TD="bgcolor: #FAFAFA"]pipes and exhausts[/TD]
[TD="bgcolor: #FAFAFA"]yes[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IF(OR(IFERROR(FIND(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),{1,100,200,300,400},50)),"|"&B2)>1,FALSE)),"Yes","No")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

This should work if column A has up to 5 words in it.

 

[ellison]

Absolutely perfick - I substituted in "search" for the "find" in the formula to make sure it didn't matter about upper or lower case - but again .... ABSOLUTELY PERFICK!

You're a gent!!!!!

Best
Neil

 

[Eric W]

Happy to help!