Find Multiple keywords from free text cell

Dear Experts, I am looking for a formula that can find multiple keywords from a cell containing free text and give result in another cell.

For example: In cell A2 in have Free Text as "I work for a company as an advisor". and I have keywords in column "Y1:Y5" as "WORK", "COMPANY", "ADVISOR", "PEOPLE" etc...
So in col B2 i want this formula to return the result as "WORK", in C2 as "Company", in D2 as "Advisor"

PS: Sorry for some reason i am not able to upload a sample excel file to explain it better.

Any help please.

Originally Posted by amitmodi_mrt

PS: Sorry for some reason i am not able to upload a sample excel file to explain it better.

Yes, you are able to. Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Assume you have list in J2:J5

try

B2

Formula:

Please Login or Register  to view this content.

Drag towards the cells.

Check the attached file.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.