Search text in another table column

[Anonymous]

Hi,

I have two tables and I want to search a text from one table to the other :

Fruits_List

Fruit_Name

Apple

Banana

Grape

Shop

Merchandize_Description

Yellow big zise Banana

Apple

Kids toy

I want to have the following column output:

Shop

Merchandize_Description	Is_Fruite
Yellow big zise Banana	Yes
Apple	Yes
Kids toy	No

I tried different formulas but I keep getting wrong output. Everything I tried gives me the right answer only when the values are exactly matching:

Merchandize_Description	Is_Fruite
Yellow big zise Banana	No
Apple	Yes
Kids toy	No

Here are the formulas I tried:

Is_Fruit = IF(Calculated(COUNTROWS(Fruits_List), FILTER(Fruits_List, FIND(Fruits_List[Fruit_Name], EARLIER(Shop[Merchandize_Description]),,-1)))>0), "Yes","No")

Is_Fruit = IF(Calculated(COUNTROWS(Fruits_List), FILTER(Fruits_List, CONSTAINSSTRING(Shop[Merchandize_Description], Fruits_List[Fruit_Name],,-1)))>0), "Yes","No")

Is_Fruit = IF(Calculated(COUNTROWS(Fruits_List), FILTER(Fruits_List, ISNUMBER(SEARCH(Fruits_List[Fruit_Name], EARLIER(Shop[Merchandize_Description]),1,Blank())))>0), "Yes","No")

I feel I am missing something. Any advice?

Thank you,

B

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=IF(SUMX(Fruits,FIND(UPPER(Fruits[Fruit_Name]),UPPER(Merchandise[Merchandize_Description]),,0)) > 0,"Yes","No")

Hope this helps.