Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Search text in another table column

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_DescriptionIs_Fruite
Yellow big zise BananaYes
AppleYes
Kids toyNo

 

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_DescriptionIs_Fruite
Yellow big zise BananaNo
AppleYes
Kids toyNo

 

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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

1 Reply

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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.