Find Words from an entire column in a cell

Hi,

I like to write a macro that finds words from an entire column in a cell and then returns a value associated with that founded word.

Here is an example:

My reference file (Sheet1 ):
column A (A2 to A5): Apple , Orange, grape, Strawberry
Column B (B2 to B5): 123, 456, 789, 001

My worksheet (Sheet 2):
Column A is Description (A2 to A5): "I love Apple" , "Strawberry//a123", "Orange4H70, huil;sf"
Column B is Code which is unknown.

I like to look at the description, then search in Column A sheet 1 , find the word Apple , and then return the code for apple which is 123.

If there are two words found in the description, i like to only consider the first one appear in the reference file.

I would appreciate any help in writing this search loop.

Thank you,

Hey,

the formula doesnt work for the last case :Orange4H70, huil;sfapple, it returns the code for grape. could you provide a loop formula that goes over all the keyword and match it with description in order to return the code value?

Thank you,