Extract substring from cell if substring is in list

by Snaarf   Last Updated October 11, 2018 16:01 PM

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.

Example



Answers 1


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.

enter image description here

Scott Craner
Scott Craner
October 11, 2018 15:47 PM

Related Questions





Select 1st day of each user id in excel

Updated December 19, 2018 07:01 AM

Text function in Excel isn't formatting year

Updated February 07, 2019 07:01 AM