Excel - show if a cell has a substring from a range of cells, showing the substring that match

Question

I'm trying to get a substring from a cell if it is contained at a range of strings.

For example, given the column A, D and E, I want to generate the column B

I searched the internet for a possible solution, but nothing solved this problem.

Examples followed unsuccessfully:

How to find if substring exists in a list of strings (and return full value in list if so)

Can you help or point to the best way to solve the problem?

Thank you

Answer 1

Using Cell contains one of many things as a starting base, I developed this to work for your situation.

=IFERROR(INDEX($E$1:$E$4,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)),"")

Note this is an array formula, so it must be entered by pressing **Ctrl** + **Shift** + **Enter** when exiting edit mode in the cell. Also, things is a named range referring to the range $D$1:$D$4