Search for a single word in a sentence and output the searched value next to the correct sentence.

[Flambo]

Hi there, first post.

I'm knees deep in excel these days, but unfortunately not overtly talented (yet).

I have the following issue:

Two columns:

A = one with a set of 1500 keywords
B = 25000 sentences of text
C = the output (which should also be the keyword)

I need to search the sentences for the keywords and next to the sentence output the value that was searched in column C.

I'm currently trying to use Vlookup with a search function inside the formula but I'm not getting very far unfortunately.

Here is an image, should be simple enough

Many thanks,

Peter

 

[Caribeiro77]

Welcome, can you post 3 or 4 examples with desired result?

 

[Flambo]

Thank you, I'm sure there is a reasonably simple solution, but its beyond me!

Thanks again.

A = The search terms
B = Sentences to be searched
C = would be where the formula/ output

 

[Caribeiro77]

hum.....think you need VBA on this....and i'm not the right guy to give a solution...
Let some VBA guru check on this...

 

[Flambo]

Ok, thanks very much for taking a look though

 

[Flambo]

So, still hoping for a solution if anyone has any ideas... you'll be saving me days and days of work!

 

[Adjunctor]

Try this, I use it to extract keywords on a file with thousands of records and it works just fine.

{=IFERROR(VLOOKUP(INDEX(kSearchs,MATCH(SMALL(IFERROR(SEARCH(kSearchs,B2),""),1),SEARCH(kSearchs,B2),0)),kSearchs,1,FALSE),"No matches found")}

Where kSearchs is a named range containing the your keywords list. I use a dynamic range so whenever I want to add keywords I just type them at the bottom of the list and the range adjust by itself, but you are OK using a fixed named range; if you don't want to use a named range just use the actual range on your formula.

If no keyword is found the formula will output "No matches found" but you can change this to whatever you want, even an empty string ("") to leave teh cell blank.

IMPORTANT! Remember this is an array formula, don't type the brackets around it, instead hod down Ctrl & Shift and then press Enter on C2. Once you enter the first formula just drag down and Excel will fill the rest of the cells.

Saludos!

 

[Adjunctor]

I forgot to mention, to find more than one keyword you will need to use other instances of the formula, changing the index on the SMALL function to 2, 3, etc. I have used it with up to 4 instances with good results. To show them separated by comas you will need to joint your instances of the formula like this =Formula1&", "&Forumla2&", ".... Formula#

A word on Array formulas: When using this type of formulas on large data bases, the excel engine has to process tons of data and it may take a few seconds to run. It doesn's bother me because I run it once a day, but if you are using this functionality on acontinuos way it might be better to look int a VBA approach.

Saludos!

 

[Flambo]

Adjunctor!

Thank you so much! Its a perfect start

Thanks so much for your time.

 

[Adjunctor]

You're welcome!

I was on your same situation a few months ago until I came up with this solution. But I'm pretty sure that extra time you'll gain won't last long LOL