Extracting specific words surrounded by data from a range of cells

[analyst0503]

Hi, I've been trying to find a method that would work for this type of extraction but just haven't come across it.

Please find the explanation below:

I have set values (in a table) in text form that I have to look for within a range of cells. I've used IFSUM formulas to tell me whether one of the words from the table is present within the cells but haven't found a way for the formulas to return the keywords when they find them. It should also be said that sometimes, the values will have the same first word but a different second - e.g. John Smith and John Jones will both be present in the table and I only want John Smith to be matched to John Smith.
Here is an example - on the right is what I want my search to return, I have a table with all the names that I'm trying to look for in the range of cells. I want Excel to tell me when there is that specific word combination and return that same combination into the desired cell.

I would prefer to be able to use a formula/function but can also give Macros a go if that's the only option (would be grateful for a detailed explanation in that case).

Many thanks in advance!

 

[Micron]

Not clear if you want to look for C1 in A1, C2 in A2, C3 in A3 etc. or look for C1 in A1:A10, C2 in A1:A10, C3 in A1:A10...
I think it is the latter, though.
Formulas are not my thing but probably could create some code. We can wait to see if you get a formula solution but I think some clarification will help either way.

 

[jtakw]

Hi,

Formula, based on your description.

Please consider posting samples with XL2BB, or at least in Table format, so helpers don't Have to type up data manually.

Book3.xlsx
	A	B	C	D	E
1	String	Match from Table			Name Table
2	abc def John Smith dskl oerha;	John Smith			Bumble Bee
3	ksoery Bumble Bee oejrpa skseo	Bumble Bee			Jack Ryan
4	skoej kseo asleo John Doe				John Smith
Sheet1079

Cell Formulas
Range	Formula
B2:B4	B2	=IFERROR(LOOKUP(2,1/SEARCH(E$2:E$4,A2),E$2:E$4),"")

 

[analyst0503]

Thank you both for your response, the formula seems to work but only on a smaller selection of data, I have about 1mil rows of names that I need to match and it crashes my Excel. If I were to use a Macro, would this make it more efficient?

Unfortunately I'm unable to download the application that would make the sharing more straight forward.

Thanks in advance!

 

[Micron]

The fact that your key words (strings like John Smith) are all over the place would make for more intensive processing. Would be much faster if they were in the same table field/column. So with 1M rows and who knows how many characters in each string it will likely take a fair amount of time, assuming it doesn't crash. Seeing as how you have something that works, try restricting the range used in the formula and doing it over batches of say, 5 or 10 thousand rows at a time. If it's fast, double the rows until it slows down but doesn't crash. Save the file after each run.

 

[analyst0503]

Thank you Micron! We'll try using that method. Thanks to both of you for your help!

 

[jtakw]

You're welcome.