Searching for unknown text string in a range and then matching it to a reference table

Originally Posted by the_nibs

if I have the following cells: <red> <blank> <blue> <blank> <blank> is there a way to search those cells to give me the result 'red' 'blue'?

Data Range

	A	B	C	D
1	Red		Red	Red, Blue
2			Blue
3	Blue
4
5	------	------	------	------

If you want the results in separate cells like in col C then we can use a formula to do it.

If you want the results to be a concatenated string like in col D then you'll need to use a VBA function to do it.

Which method do you want to use?