There are different ways to look for a specific word in two tables, the only difference is that one will work better than the others based on my data. I have those 2 tables:

Table1	Table2

I will resume quickly what I can do with table1 only:

• SEARCH
  • With 1 criteria
    IF(ISERROR(SEARCH("XXX",'table'[argument])),"not found","found")
    
  • With 2 or more
    IF(ISERROR(SEARCH("XXX",'table'[argument1]) && SEARCH("YYY",'table'[argument2])),"not found","found")
    
• CONTAINSSTRING
  • With 1 criteria
    IF(CONTAINSSTRING('table'[argument],"XXX"),"found","no found")
    
  • With 2 or more
    IF(CONTAINSSTRING('table'[argument1],"XXX") && CONTAINSSTRING('table'[argument2],"YYY"),"found","no found")
    

NOTE: replace “table” and “argument” by yours and XXX and YYY by the values to find

With the 2 tables, the easier is to use:

• LOOKUPVALUE
  • With 1 criteria
    LOOKUPVALUE('table1'[argument1],'table1'[argument2],'table2'[argument2])
    
  • With 2 or more
    LOOKUPVALUE('table1'[argument1],'table1'[argument2],'table2'[argument2],'table1'[argument3],
    'table2'[argument3])
    

NOTE: replace “table1” and “table2” by yours, “argument1” by the result column and “argument2” and “argument3” by the columns to match

As we can see, it is not looking the word into a sentence or a text, so I will use one of those 2 formulas:

• With SEARCH
  LOOKUPVALUE('table2'[argument1],'table2'[argument2],FIRSTNONBLANK(FILTER(VALUES('table2'[argument2]),
  SEARCH('table2'[argument2],'table1'[argument2],1,0)),1))
  
• With CONTAINSSTRING
  LOOKUPVALUE('table2'[argument1],'table2'[argument2],FIRSTNONBLANK(FILTER(VALUES('table2'[argument2]),
  CONTAINSSTRING('table1'[argument2],'table2'[argument2])),1))
  

NOTE: for the cells which are blank, if I want to put a value, I have to use IF and ISBLANK functions like that:

IF(ISBLANK(formula),"not found",formula)

There are other ways to get the same results, based on my data, one will work better than the others, for instance:

• MINX(FILTER('table2',SEARCH('table2'[argument2],'table1'[argument2],1,0)>0),'table2'[argument1])
  
  NOTE: instead of MINX, I can use MAXX
  
• CONCATENATEX(FILTER('table2',CONTAINSSTRING('table1'[argument2],'table2'[argument2])),'table2'[argument1],",")
  
• IF(COUNTROWS(FILTER('table2',CONTAINSSTRING('table1'[argument2],'table2'[argument2]))),"found","not found")
  

All formulas above can return the values of any columns of the Table2 but if I want to get only the ones from the “number” column which have the same values as the “incident” column of the Table1, one of those 2 formulas will work better:

• With SEARCH
  FIRSTNONBLANK(FILTER(VALUES('table2'[argument]),SEARCH('table2'[argument],'table1'[argument],1,0)),1)
  
• With CONTAINSSTRING
  FIRSTNONBLANK(FILTER(VALUES('table2'[argument]),CONTAINSSTRING('table1'[argument],'table2'[argument])),1)