Partial lookup from text string, return value from lookup list

I will probably explain this incorrectly, but here goes.

I'm looking for a "fuzzy Lookup" formula? that can look at a long string of text and pull out what matches from a list and return the value from the list. Is that possible?

Example is attached.

Try

=INDEX($A$2:$A$50,MATCH(TRUE,ISNUMBER(SEARCH(G2,$A$2:$A$50)),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Try this in B2 ...

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(Before!$B$2:$D$10=$A2,ROW(Before!$B$2:$D$10)-1,""),COLUMNS($A$1:A$1))),"")

Enter with Ctrl+Shift+Enter, copy across and down.

I cannot get any of these to work.

Just to recap, column A is my long string and I want to match the appropriate Team Name from Column G to Column A (put answer in column B).

Try this in B2

=IF(OR(ISNUMBER(SEARCH($G$2:$G$6,A2))),A2,"")

Array confirmed with Shift Ctrl Enter.

Does that do what you need? We would have a better idea what we were aiming for if you entered the expected results into your example.

I have attached my original copy and added a column that says "expected answer". Please review the attachment to see if what I need is possible.

Thank you so much.

This will work for the first 3 team names, but not the last 2. Similarly, I think that any formula which will work with the last 2 will not work with the first 3.

Using a short string as a partial match with longer strings is easy enough, but trying to match a partial string to a partial string is going to be error prone given that there are common words in the other team names.

=IFERROR(INDEX($G$2:$G$6,MATCH(1E+100,SEARCH($G$2:$G$6,A2))),"")

Array confirmed with Shift Ctrl Enter