Question: I would like to create some kind of LOOKUPVALUE on text in DAX that will match a sentence with a particular keyword. For instance in the example below the second and third row have a hit because “Apple” and "Chicken" is in the string. The problem is that the text is in a string and not a standalone value.

**Table 1**

[![enter image description here](https://i.stack.imgur.com/kbSEM.png)](https://i.stack.imgur.com/kbSEM.png)

**Table 2**

[![enter image description here](https://i.stack.imgur.com/fX9fU.png)](https://i.stack.imgur.com/fX9fU.png)

**Output**

[![enter image description here](https://i.stack.imgur.com/mCUqc.png)](https://i.stack.imgur.com/mCUqc.png)

Answer: 
**EDIT, improved answer:** this new version also works when there are multiple keys in one string. 

I think PowerQuery is the natural place to perform an operation like this.

The Output table would look like this:

[![enter image description here](https://i.stack.imgur.com/uV6Al.png)](https://i.stack.imgur.com/uV6Al.png)

A description of the applied steps:

1. Source: a reference to Table1
2. Added Column Key lists: adds a custom column with lists of the Table2[Key] value(s) that are in the [String] value. This is the logic for this Custom column:

[![enter image description here](https://i.stack.imgur.com/bvOit.png)](https://i.stack.imgur.com/bvOit.png) 

For each row the function selects the values from the Table2[Key] column that it finds in the [String] value. It then returns a list that holds only the selected values. 

3. Expanded Key list: expands the lists in the [Key] column
4. Join with Table2 on Key: Joins with Table2 on the Key Value
5. Expanded Table2: Expands the table values in the [ItemTables] column and keeps the [Item] column
6. Group and concate keys/items: Groups the Output table on String, concatenating the Keys and the Items. If you don't want to see the [Key] column, delete `{"Key", each Text.Combine([Key], " | "), type text},` from this step

The script in the Advanced Editor looks like this:

```
let
    Source = #"Table1",
    #"Added Column Key lists" = Table.AddColumn(Source, "Key", (r) => List.Select(Table.Column(Table2,"Key"),each Text.Contains(r[String],_,Comparer.OrdinalIgnoreCase)),type text),
    #"Expanded Key lists" = Table.ExpandListColumn(#"Added Column Key lists", "Key"),
    #"Join with Table2 on Key" = Table.NestedJoin(#"Expanded Key lists", {"Key"}, Table2, {"Key"}, "ItemTables", JoinKind.LeftOuter),
    #"Expanded ItemTables" = Table.ExpandTableColumn(#"Join with Table2 on Key", "ItemTables", {"Item"}, {"Item"}),
    #"Group and concate keys / items" = Table.Group(#"Expanded ItemTables", {"String"},{{"Key", each Text.Combine([Key], " | "), type text},{"Item", each Text.Combine([Item], " | "), type text}})
in
    #"Group and concate keys / items"

```

Here is a [link](https://www.dropbox.com/s/gy60pw3adqs78kd/SO%207-20-2019.pbix?dl=0) to my .pbix file
Try this formula (see the picture which cell is where in my assumptions):

```
=IFERROR(INDEX($B$7:$B$9,MATCH(1,--NOT(ISERROR(FIND($A$7:$A$9,$A12))),0)),"-")

```

[![enter image description here](https://i.stack.imgur.com/osniM.png)](https://i.stack.imgur.com/osniM.png)