Hi MS Community,

I have the following worksheet. In the Orders Tab, I need to be able to filter out the “Fruit” from the “Sentence”.

Sheet1 = Fruits

Sheet2 = Orders

In Fruits:

 A1apple2pear3banana

In Orders:

 SentenceFruit1James buy appleapple2Peter sell pearpear3Banana from japanbanana4apple keeps the doctor awayapple5Do you sell durian?Invalid

Preferably, casing can be ignored. I was trying out Index & Match but it didn’t work (pls ignore the rows and column number as I was trying to simply using fruits).

=INDEX(fruit!A1:A3,MATCH(“*”&fruit!A1&”*”,C1:C4,0))

Kind Regards

​Hi MS Community, I have the following worksheet. In the Orders Tab, I need to be able to filter out the “Fruit” from the “Sentence”. Sheet1 = FruitsSheet2 = Orders In Fruits: A1apple2pear3banana In Orders: SentenceFruit1James buy appleapple2Peter sell pearpear3Banana from japanbanana4apple keeps the doctor awayapple5Do you sell durian?Invalid Preferably, casing can be ignored. I was trying out Index & Match but it didn’t work (pls ignore the rows and column number as I was trying to simply using fruits). =INDEX(fruit!A1:A3,MATCH(“*”&fruit!A1&”*”,C1:C4,0))   Kind Regards  Read More

​