Search a cell for a value that exists in a list to pull a value in Excel-CodePudding

In Excel, I have a list of strings that within the cells contain the name of a State. I then have a list of the States and I want to have a formula that is able to search the string for a State name and then give me the name of the State.

I've used nested =IF(COUNTIF(A1,"*Florida*")=1,"Florida"...) in the past for similar exercises but I don't want to create a version for all 50 States. Is there a way to do this combining some kind of INDEX MATCH?

Image below is a snippet of the kind of data. For the most part, the State name follows the year but not always and the suffix isn't always Invitational so there's no way to use those two to book-end the part of the string that contains the State.

Any help would be appreciated!

Formula in B1:

=FILTER(D$2:D$4,COUNTIF(A1,"*"&D$2:D$4&"*"))

Or:

=FILTER(D$2:D$4,ISNUMBER(FIND(D$2:D$4,A1)))

CodePudding user response：

Assuming you have the filter function, here's a clean short formula. Assume Column E:E has your list of states, and then just reference the cell...

=Textjoin("",true,FILTER(if(isnumber(Search(E:E,A2)),E:E,),E:E<>""))

Updated:

Stealing from JvDV this is probably a better version of what I tried to setup:

=FILTER(E:E,(isnumber(SEARCH(E:E,A2))*(E:E<>"")))

CodePudding user response：

Use this Formula

=IF(ISNUMBER(VALUE(LEFT(A1,1))),MID(A1,FIND(" ",A1) 1,
FIND("~", SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-(FIND(" ",A1) 1)),
LEFT(A1,FIND("~", SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))