Excel 2007 : Finding substring in array of possible strings

[kkerr]

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!

[NBVC]

Assuming data starts in Row2, then in B2:

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

copied down.

[kkerr]

This worked beautifully! Many thanks!

[ExcelChemist]

When the string length exceed 255 characters, the MATCH function will error out.

For example, let's say I have in column A a list of authors. In column B, I have a quotation from each author that is longer than 255 characters.

In C1, I type a short sub-quote. In D1, I'd like to see which author wrote the text I typed into C1.

The formula entered in D1

{=INDEX(A1:A4,MATCH("*"&C1&"*",B1:B4,0)}

returns #N/A if the string is longer than 255. This is entered with CTRL+SHIFT+ENTER, of course.

I know that this is a fundamental limit on several of Excel's functions. Any ideas on a work-around?

[NBVC]

Please start your own new thread.