VLookUP type but reverse substring Matching

[Mitesh9]

I have data in 2 Columns.
A: Addresses
B: List Of Different Areas

Column A will be much much longer (more cells/rows) than column B.

Like this:

A	B
opp-don bosco school,jivraj park	Shyamal
pearl apartments, opp. shakti school, near shyamal cross roads, satellite	Ramdevnagar
orcid park , b/h shalby hospital ramdevnagar,ahmedabad.	Jivraj
i-404 kala residency, times of india press road Shyamal

<tbody>
</tbody>

I want Column C to be filled with such a formula, that will look up into each entry of Column A, and find if any area from Column B (Area List) is part of the full address. If it is, it will put the area name in that cell of C, else it will keep it blank.

The result should be like this:

A	B	C
opp-don bosco school,jivraj park	Shyamal	Jivraj
pearl apartments, opp. shakti school, near shyamal cross roads, satellite	Ramdevnagar	Shyamal
orcid park , b/h shalby hospital ramdevnagar, ahmedabad.	Jivraj	Ramdevnagar
i-404 kala residency, times of india press road		Shyamal

<tbody>
</tbody>

I hope I have mentioned this problem clearly enough.

Basically, this is like reverse VLookup. I want to find if any of the word from column B appears in individual cells of Column A, and if it does, I want to put that word in Column C in respective row.

Thank you for your time...

 

[circledchicken]

Hi and welcome to the forum,

The result in C4 of your example doesn't look right, but perhaps try something like this:

Excel 2013
	A	B	C
1	opp-don bosco school,jivraj park	Shyamal	Jivraj
2	pearl apartments, opp. shakti school, near shyamal cross roads, satellite	Ramdevnagar	Shyamal
3	orcid park , b/h shalby hospital ramdevnagar, ahmedabad.	Jivraj	Ramdevnagar
4	i-404 kala residency, times of india press road		#N/A
Sheet1

Cell Formulas
Range	Formula
C1	=LOOKUP(2, 1 / SEARCH(B$1:B$3, A1), B$1:B$3)

 

[Mitesh9]

circledchicken,

Thank You for Welcoming me and a Quick Response. I had been struggling with this for hours.

You are right. There was problem with the data I posted. It was copy/paste error.

Your solution is very elegant. It works for me as I expected to.

Many thanks.

 

[circledchicken]

You're welcome - and thanks for the feedback.

(It's not really my solution - I found it on this forum, explained by Aladin Akyurek here: http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html).