Partial Match Cell To String Array

Morning all,

I might struggle to explain this one, so please bear with me if the question is unclear.

I'll elaborate below, but this is the end result I want to see:

A	B	C
Dark Green Box	RED	GREEN
Yellow Flower Pot	YELLOW	YELLOW
Gold Coin	GREEN	GOLD
Blue Fish	BLUE	BLUE
Cured Ham	BLACK	RED
	WHITE
	GOLD
	SILVER

Columns A and B are populated by me, I want a formula in column C to look at the contents of column A on the same row, and see if anything in all of column B matches. If it does, I want it to return the contents of column B.

In reality, column B is in a separate worksheet, but I assume the formula is much the same even if the columns are right next to each other.

Any ideas on how this can work?

Hi,

Assuming that the table you give is in the range A2:C9, enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in C2 and copy down as required:

=IFERROR(INDEX($B$2:$B$9,MATCH(TRUE,ISNUMBER(SEARCH($B$2:$B$9,A2)),0)),"")

You don't mention (or give examples of) cases in which more than one of the entries in column B is present in an entry in column A, so I assume that this is never the case.

Regards

You're welcome.

In the case of more than one match, the best approach would be to allow one cell for each match, e.g. extended in columns to the right. In that case the formula (again, array-entered) in C2 would become:

=IFERROR(INDEX($B$2:$B$9,SMALL(IF(ISNUMBER(SEARCH($B$2:$B$9,$A2)),ROW($B$2:$B$9)-MIN(ROW($B$2:$B$9))+1),COLUMNS($A:A))),"")

for which, e.g. "Gold and Black and Silver and Red Coin" in cell A4 would return, based on your list in column B, "RED", "BLACK", "GOLD" and "SILVER" in cells C4, D4, E4 and F4 respectively.

Concatenating all matching results into a single cell is a different matter and one which would require a VBA-based solution.

Regards