Check if Cell Contains any values from a list, partial match

[scottclayton]

Hi!

I'm trying to find if a cell contains a partial text match to a list of text values in a range of cells.

For example,

Let's say cell a1 contains a really long text-value:

A1
="If horse-racing is the sport of kings then surely bowling is a… very good sport as well"

And cells b1:b3 are
="bowling"
="lacrosse"
="badminton"

if I want to check to see if any of the words in b1:b3 are contained in a1, is there a way to do that? It's sort of like a mini-database lookup.

I've searched for a long while, and, over the years this site has been invaluable to me, but I haven't been able to find any solutions for this one as of yet.

Thanks!

[Pepe Le Mokko]

Perhaps in C1 something like =if(isnumber(search("*"&B1&"*",$A$1)),"contained","not contained") and pull down as needed

[Fotis1991]

Try

=IFERROR(LOOKUP(2^15,SEARCH(B1:B3,A1),B1:B3),"")

[scottclayton]

Thank you so much to both Pepe Le Mokko and Fotis1991! Ultimately, I find Fotis1991's solution to be the most robust for my purposes here. This is very powerful, and using this function, along with a bunch of others I've been piecing together, coupled with a couple hundred hours of my labour, Excel will be able to solve serious problems and feature requests that our database software company hasn't been able to solve in over ten years, as well as likely hundreds of hours per labour annually, and have lots of fun too!

Thanks again to both of you!

[Fotis1991]

........................