find partial match of string between two arrays

[codingchemist]

I have two arrays. Array A has a list of multiple terms in a column such as this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Urinary Bladder Cancer|Bladder Tumors|Transitional Cell Carcinoma of the Bladder|Malignant Melanoma|Melanoma|Skin Cancer|Carcinoma, Non-Small-Cell Lung|Lung Cancer[/TD]
[/TR]
[TR]
[TD]Platinum Sensitive Ovarian Cancer|Ovarian Cancer[/TD]
[/TR]
[TR]
[TD]Gastric Cancer[/TD]
[/TR]
</tbody>[/TABLE]

Array B has a list of single terms such as this:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Gastric Cancer[/TD]
[/TR]
[TR]
[TD]Ovarian Cancer[/TD]
[/TR]
[TR]
[TD]Bladder Cancer[/TD]
[/TR]
</tbody>[/TABLE]

I want to map the terms from Array B onto Array A. So, I want Array A to look like, with the terms from Array B included in it.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Urinary Bladder Cancer|Bladder Tumors|Transitional Cell Carcinoma of the Bladder|Malignant Melanoma|Melanoma|Skin Cancer|Carcinoma, Non-Small-Cell Lung|Lung Cancer[/TD]
[TD]Bladder Cancer[/TD]
[/TR]
[TR]
[TD]Platinum Sensitive Ovarian Cancer|Ovarian Cancer[/TD]
[TD]Ovarian Cancer[/TD]
[/TR]
[TR]
[TD]Gastric Cancer[/TD]
[TD]Gastric Cancer[/TD]
[/TR]
</tbody>[/TABLE]

Normally I would do an index match, but I need to find a partial match from Array A to Array B. Is there an easy way to do this?

 

[Aladin Akyurek]

Book1
	A	C	D
1	Gastric Cancer	Urinary Bladder Cancer|Bladder Tumors|Transitional Cell Carcinoma of the Bladder|Malignant Melanoma|Melanoma|Skin Cancer|Carcinoma, Non-Small-Cell Lung|Lung Cancer	Bladder Cancer
2	Ovarian Cancer	Platinum Sensitive Ovarian Cancer|Ovarian Cancer	Ovarian Cancer
3	Bladder Cancer	Gastric Cancer	Gastric Cancer
Sheet1

In D1 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$1:$A$3&"|"," "&$C1&"|"),$A$1:$A$3)