Thread: Finding substring in array of possible strings

Hello,

I need a formula to search for a substring in an array of possible strings, then output the matching string.

Example:

Col A contains the substrings I'm looking for:

BLACK
BLUE
GREEN
ORANGE
PURPLE
RED
WHITE
YELLOW

Col B will output the matching string it finds

Col C has the array of possible string matches, for example:

Conduct IMT - (RED)
Conduct IMT - Group 101A (BLUE, YELLOW, PURPLE)
GREEN - Conduct IMT - Group 14
Attend Meeting - Group 16 - WHITE, BLACK, ORANGE

So, I want the formula in Col B to tell me for the first one in Col A (Black), it will output the string "Attend Meeting - Group 16 - WHITE, BLACK, ORANGE"

Thanks very much!

Assuming data starts in Row2, then in B2:

=INDEX($C$2:$C$5,MATCH("*"&A2&"*",$C$2:$C$5,0))

copied down.