Compare text in a field with a table, using lookup and logic

[Cristiano_Volpi]

Hello Community, please help to solve in an elegant way!
I have record, and in a field called "FOOD" I have text separated by comma, let's say in the text I have

"FOOD" = Apple, Carrot, sausage

I then have another table caleld "FOOD CATEGORIES" with all the food separated by categories like (Can be a table for each category, same situation):

Apple - FRUIT 

Banana- FRUIT 

Mango- FRUIT 

Carrot - VEGETABLE

Zucchini - VEGETABLE

Potato - VEGETABLE

Sausage- MEAT

Beef- MEAT

Chicken- MEAT

IceCream - DESSERT

Cheescake - DESSERT

Candy - DESSERT

So now I want that in the field next to the field "FOOD" will be a field called "FOOD CATEGORIES" where it will appear the food categories according to the table classification, the previous example, I want to see CATEGORIES FRUIT, VEGETABLE, MEAT

At first I tough I could do with a super concatenation of IF, but seems not very elegant approach.
The real challenge is that in the field food, could be a lot of different items, so I want that the match will be done for all the times, and each categorise will appear only once (In the field I can have 5 different fruit, and 3 differnt meat, I want ot see only one time FRUIT and only one time MEAT)
Any advise on the approach will be very welcome!
Thank you much in advance!

[Andy_Lin1 · Accepted solution]

The easiest way to do it is to create a separate table with your food items (e.g. "IceCream") as the primary field and then their category as a single-select field. And then in your original table, you'd use a linked record field instead of a text field. You could then get the category via a lookup/rollup field.

If you don't want to create a separate table (say you're on a free plan and you want to maximize the number of records of actual data), then your best bet isn't IF (which works best with single items), but SUBSTITUTE, which... gets illegible fast. Part of the issue is that Airtable doesn't have array formulas (or functions like SPLIT), so you can't easily run a formula over a range of items within the same cell.

[Cristiano_Volpi]

Nice!
Thank you very much!
I managed with a roll, and arraunique in order to avoid duplicate association