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:

Coed 5v5 New Years Soccer Challenge	Soccer
Coed 5v5 New Years Volleyball Challenge	Volleyball
Grand finale Badminton 2014	Badminton
2014 Football League Championship	Football

<tbody>
</tbody>

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>

Soccer

Volleyball

Badminton

Football

<tbody>
</tbody>

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?