excel: how can I identify rows containing text keywords taken from a list of keywords

Question

I have one column (call it A) of data where each cell contains a long string of words e.g.:

COLUMN A 
HORNBACH BAUMARKT ETOY, ETOY 
ALIGRO, CHAVANNES-PR 
DIPL. ING. FUST AG,ETO, ETOY 
AGIP SUISSE SA 224, LAUSANNE 
AMAZON MEDIA EU, LUXEMBOURG 
MIGROS M EPALINGES, EPALINGES 
HORNBACH BAUMARKT ETOY, ETOY 
MANOR AG - 390, BASEL 
MANOR AG - 390, BASEL 
GLOBUS LAUSANNE, LAUSANNE

I also have another list of keywords in another column (call it B) e.g.

COLUMN B 
MSFT 
Amazon 
Hornbach 
Jumbo 
OBI 
Lipo 
Ikea 
Coop 
Migros 
Casino

This is what I would like to do:

For each keyword K in Col B Check each cell in Col A to see if the entry exists as a sub-string If it does, then enter the keyword K in an adjacent cell in Column C If not, then leave the adjacent cell in Column C untouched Repeat for next keyword K

The result should be:

COLUMN A ----------------------------------> COLUMN C 
HORNBACH BAUMARKT ETOY, ETOY --------------> Hornbach 
ALIGRO, CHAVANNES-PR 
DIPL. ING. FUST AG,ETO, ETOY 
AGIP SUISSE SA 224, LAUSANNE 
AMAZON MEDIA EU, LUXEMBOURG ---------------> Amazon 
MIGROS M EPALINGES, EPALINGES -------------> Migros 
HORNBACH BAUMARKT ETOY, ETOY --------------> Hornbach 
MANOR AG - 390, BASEL 
MANOR AG - 390, BASEL 
GLOBUS LAUSANNE, LAUSANNE

I can see how to do this using VBA type structures... but surely there must be a way to do this using built in Excel functions - INDEX, HLOOKUP, SEARCH... etc.

I've tried but not succeeded. If anyone has a better idea, please let me know.

Solution

Assuming only 1 matching word per row at most you could use this formula in C1 copied down

=IFERROR(LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10),"")

IFERROR function is available in Excel 2007 or later versions only, for earlier Excel versions try this modification

=LOOKUP("zzz",IF({1,0},"",LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10)))

If you want to get multiple matches, in separate cells then you can use this "array formula" in C1, confirmed with CTRL+SHIFT+ENTER and copied down and across as far as you might need (commensurate with the maximum possible matches)

=IFERROR(INDEX($B$1:$B$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1:$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")

If A1 contains 3 words on the list then those will be populated in C1, D1 and E1 and F1 etc. will remain blank

Revised as per comments:

The first range in the formula (first argument of INDEX), defines the range from which the result is taken so to change that to column Z just change that part, i.e.

=IFERROR(INDEX($Z$1:$Z$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1:$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")

Comments

[Joe137]

Is it possible to have multiple results in one cell, separated by a character? Example: outcome would be "Ikea|Lipo|Coop" in one cell.

[Kunjan]

Barry... thank you very much. The array formula works a treat although my head is still spinning trying to understand the algorithm. Not sure what an exponent does in the 1st two formulas (2^15) - I guess here the caret does something other than raise to the power of. I have an additional clarification, is there a way to modify the array formula so that it can pick up a result from a corresponding column e.g. column Z which contains a different keyword list. In other words, check against keyword list in column B, but pick value from matched row in column Z. Many thanks.

[barry houdini]

Yes, 2^15 is 2 to the power of 15 = 32768, used here because it's guaranteed to be greater than any number returned by SEARCH function (because max number of characters in a cell is 32767). It could easily be any "Bignum" e.g. 100000. To return from another column change the first range in formula - I revised my answer to reflect that

[Stepan1010]

The reason this is so unintuitive is because SEARCH actually returns an array of results when the find_text parameter is a range of cells (a weird functionality of the SEARCH function): =IFERROR(LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10),"")

[Kunjan]

Hello Barry and Stepan1010 --- thank you very much indeed for your suggestions and help. I hope that I can pay it forward...! Regards.

[barry houdini]

@Stepan1010, SEARCH certainly isn't the only function that behaves that way, you can replace any 1 of the three arguments of SEARCH with an array or range and the result will be an array - you can also do the same with FIND, or SUBSTITUTE or many others

[Stepan1010]

By "weird" I meant that alot of excel users are not aware of that functionality. I personally wasn't before I did some research into your function above (which I congratulate you on winning that Mr. Excel competition back in 2008 for). But, it is extremely useful and I hope to utilize it in the future. Although it is built into these functions(and others apparently), there is obviously a way to replicate this functionality with array formulas but I really like the simplicity that is built into these functions' ability to produce array results on their own. So, basically thanks for your help.