Extract substring from cell if substring is in list

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.

0 Votes

Scott Craner
October 11, 2018 15:47 PM