Search for multiple text and return multiple values

[MagiCarty]

So I have column S in which each cell has a description of a sport program like "Coed 5v5 New Years Soccer Challenge" and I want to create a new column AE that searches column S for a list of key sports words from column AG "Soccer","Volleyball",etc. When we find "Soccer" or "Volleyball" in column S, I want to return just those names "Soccer","Volleyball",etc.

I feel like this would be super easy with an array, but I'm just terrible at them. Please help me internets.

Many thanks.

 

[cyrilbrd]

Given in A1:
[TABLE="class: grid, width: 364"]
<tbody>[TR]
[TD]Coed 5v5 New Years Soccer Challenge[/TD]
[TD]Soccer[/TD]
[/TR]
[TR]
[TD]Coed 5v5 New Years Volleyball Challenge[/TD]
[TD]Volleyball[/TD]
[/TR]
[TR]
[TD]Grand finale Badminton 2014[/TD]
[TD]Badminton[/TD]
[/TR]
[TR]
[TD]2014 Football League Championship[/TD]
[TD]Football[/TD]
[/TR]
</tbody>[/TABLE]

Given in any column of your choice the following list, example in G1 to G4:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> [TABLE="class: grid, width: 65"]
<tbody>[TR]
[TD="width: 65"]Soccer[/TD]
[/TR]
[TR]
[TD]Volleyball[/TD]
[/TR]
[TR]
[TD]Badminton[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[/TR]
</tbody>[/TABLE]

1- create a Named range 'list' containing G1:G4
2- in B1 type =IF(ISNUMBER(FIND(list,$A1)),list, "") copied down till needed.

Adjust the range to fit your requirement.

Would that work for you?