Check a Single Cell to See if Any Words from an Array are Present

ExcelForum Example.xlsx

Attached you will find Column A with some phrases. In cell B2 I need a formula but I don't know what that formula is.

What are you trying to do?

I am trying to look in cell A2 to see if it contains any WHOLE WORDS from column F. If TRUE, display the found word from column F.

Capturez.PNG

I realize that there could be multiple found words within a phrase within Column A. This is not important. What's important is if it has at least 1... more is fine and I don't need to know what those other phrases are.

In Cell B8 I provided an example output.

The formula looked in cell A8 to find if any phrases exist from column F. Notice in F2, the phrase 'episode guide' does exist? Therefore, the formula found this phrase within A8 and puts 'episode guide' in cell B8 as the result.

I don't even know how to describe this formula. I'm very familiar with lookups and vlookups and index match formulas but this is like looking up an array of words within a single cell to see if a phrase is found - not on the entire cell necessarily but on a phrase within the cell.

Hope someone can help.

Put this array* formula in B2:

Formula:

Please Login or Register  to view this content.

then copy down, as in the attached file.

* An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Hope this helps.

Pete

Pete, that helped a lot! I thought it would be an isnumber(search()) or some variation but I would have never come up with your formula you provided. Thanks a ton Pete. Your solution is exactly what I needed.