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





Listbox, mouse wheel scroll issue

Updated October 22, 2015 08:00 AM

Formula issue Microsoft Excel

Updated July 02, 2015 16:00 PM