Vlookup value against a table array

[CGJoel]

Hello, 

I was hoping someone could assist me in figuring out a way to create a new column of pre-determined group names.

1. TABLE1: List of reviews of people using multiple words to describe their experience

2. TABLE2: List of review groups and multiple words underneath

I would like to create a new column in TABLE1 that creates a "review group" 

- it would search for keywords in TABLE1 ColumnA for a reference table in TABLE2 either columns and depending on the keywords, the new column will state "Positive" or "Negative"

Is that a possibility?

J.

[DataInsights]

@CGJoel,

Try this calculated column in Table1:

Review Group = 
VAR vCurrentReview = Table1[Review]
VAR vPositiveWords =
    FILTER ( Table2, NOT ISBLANK ( Table2[Positive] ) )
VAR vPositiveMatch =
    SUMX ( vPositiveWords, SEARCH ( Table2[Positive], vCurrentReview, 1, 0 ) )
VAR vNegativeWords =
    FILTER ( Table2, NOT ISBLANK ( Table2[Negative] ) )
VAR vNegativeMatch =
    SUMX ( vNegativeWords, SEARCH ( Table2[Negative], vCurrentReview, 1, 0 ) )
VAR vResult =
    SWITCH (
        TRUE (),
        vPositiveMatch > 0
            && vNegativeMatch = 0, "Positive",
        vPositiveMatch = 0
            && vNegativeMatch > 0, "Negative",
        vPositiveMatch > 0
            && vNegativeMatch > 0, "Positive and Negative",
        vPositiveMatch = 0
            && vNegativeMatch = 0, "No match"
    )
RETURN
    vResult