I'm trying to extract a substring from a string only if that substring is in a list. See screenshot:
Column A
Director,XYZ
Partner ABCDEF
Associate ABC
Column B
Director
Partner
Associate ABC
Column C
Partner
Manager
Director
Janitor
If the string in column A contains any of the strings in the list of column C, I want that substring next to it in column B. "Associate ABC" isn't found in the list, so it doesn't change. The other 2 entries contain something in the list from column C, so they are changed to only their match in column C.
Use:
=IFERROR(INDEX(C:C,AGGREGATE(15,7,ROW($C$1:$C$4)/(ISNUMBER(SEARCH($C$1:$C$4,A1))),1)),A1)
This will iterate the desired substrings and return the first that matches. If no matches are found then it will error. The IFERROR then returns the actual string.