How to use logical formula and retun back an array instead of just 1 cell

[harsha_1991]

Hello experienced and talented Excel people.
Example. I am working for a company and I have segments, brands and variants.

Example:
Segment:Coffee, tea, chocolate

Brands: coffee--> Nescafe, ground coffee
Tea --> green tea, freshly cut leaf
Chocolate ---> white, dark, 100 cocoa

Variants:
coffee: Cold coffee, hot coffee, on the rocks coffee
Tea: aromatic, arabian
chocolate: hot chocolate, cold, chocolate smoothie

then in excel, I will have a column with Coffee, Tea, Chocolate. How can i do a logical check and return back with an array( meaning, I need all the variants that fall under the segment)
If i have a cell with "coffee", i need to write a formula that returns with an array having "Cold coffee, Hot coffee, on the rocks coffee"

Is it possible?

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html