Fetch data on comparing from keyword

Hi

I need help in finding a way to extract data by comparing one column and key words in another sheet. Below is what trying to extract. Please help…

Sample date is provided in attached excel.

Worksheet “Data” contains 2 columns “Name - Value” and “Result”.
Column “Name – Value” contains name and value separated by “-“. It might also contain multiple names and values separated by “;”.

Some names might not have values alongside to it.

Column “Result” is where we are trying to expect the answer.

Another worksheet “Keyword” contains the names list which is to be used as keywords.

Now what is expected: Under column “Result” we need:

• The Name part which is reflecting in “Name-value” column. Provided it is available in the Keyword sheet.
• The Result should contain the Name part as it is in the keyword sheet.
• Sheet “Data” , Column “Data – Value” will not be case sensitive. Whichever case the data might be, the column “result” should fetch answers in same case as it is in “sheet “Keyword”.
• If “Data – Value” has a Name which is not a part of “Keyword” then column “Result” should fetch answer as “Others”
• Sheet “Data” , Column “Data – value” may contain more than one name and value separated by “;”. Then “Result” column should have the first of the Name under “Data – value” which is a part of “keyword”.
• Note: “Result” column should fetch one the name as it is in the sheet “Keyword”.

This is sample data. Actual data has around 1500 values and 50 keywords.

Hi

Please help on this query of the desired output could be achived. It will help a lot if it works.

Please help..

Regards
xcube1414.