how to find range of words in a list of rows

[rj_81]

I m very new to this forum and having very basic knowledge of excel..

i m looking for a solution either it be any formula, function or macros.

i have range of words which i need to find in the list of strings to obtain the desired result, i have attached a sample sheet to clarify my points.
any help would be highly appreciated.

Thanks

[Olly]

Logic check: why does row 6 return "blue", when you are looking for exact matches? "blue" doesn't exist as an exact match in your [range of words].

[rj_81]

Sorry for the typos Olly the range also contain blue along with light blue.

[José Augusto]

Hi

See if helps you.

The array formula provides only one word for column.
Try in B2 and copy across and down

Please Login or Register  to view this content.

array formula must be entered with Ctrl+Shift+Enter

[Olly]

Okay.

Are you still using Excel 2010? Do you have Power Query addin installed?

Here's a Power Query solution:

Please Login or Register  to view this content.

[rj_81]

Okay.

Are you still using Excel 2010? Do you have Power Query addin installed?

Here's a Power Query solution:

Please Login or Register  to view this content.

Yes, Now i do have power query addin.

[Akuini]

Have you found a solution (by using the power query)?

[rj_81]

Have you found a solution (by using the power query)?

Hi Olly and Akuini, Actually i don't know how i can use the above solution with power query. It would be much appreciated if i could get a detailed information.

Thanks in advance

[Olly]

See the link in my signature.

[rj_81]

See the link in my signature.

Hi Olly That actually worked. Just a little confusion, should i need to repeat the entire process every time or is there anyway to save your query and access its functionality from the ribbon. Hope you understand my concern.

[Olly]

Simply refresh the connection.