Need help with looking up multiple values from an unrelated table

[ScORE]

I need help!  I have a table with multiple values listed, seperated with commas. I need to look up the category, but it is in a seperate unrelated table.  I want it to return all of the values from the category for each item in the cell.  Ideally, I would like to have just one category returned, even if the category has two food matches.

Column in my table

Food

Apples, Cherries, Celery, bread

Apples, Cheese, Celery, bread

Desired output

Value I want from look up

Fruit, Vegetable, Grain

Fruit,Dairy, Vegetable, Grain

look up from this table

Food	Category
Apples	Fruit
Cherries	Fruit
Celery	Vegetable
Bread	Grain
Cheese	Dairy

[DataInsights · Accepted solution]

@ScORE,

Try this calculated column in MainTable (your first table):

Categories = 
VAR vCategories =
    FILTER ( LookupTable, CONTAINSSTRING ( MainTable[Food], LookupTable[Food] ) )
VAR vDistinctCategories =
    SUMMARIZE ( vCategories, LookupTable[Category] )
VAR vResult =
    CONCATENATEX ( vDistinctCategories, LookupTable[Category], ", " )
RETURN
    vResult

[ScORE]

Worked Phenomonally!  Thank you!  My apologies for the delay responding, unfortunately I have to blame Spring Break 😎