Filter by Partial String in a List

[knnwndlm]

Hi SME,

I have a list that contains partial string that I want to look up against the raw data and filter out rows that contains the partial string.  Attached is an example where the column C in the Raw_Data tab has the raw data with all kinds of string.  I want to look up the table in the Lookup_Value tab to filter out anything in the Raw_Data tab that has the partial string in the Lookup_Value.

What is the most efficient way for me to do this?  I could do this the long way but want to learn best practice.  

Appreciate it.

Thanks,

kwl

[alexnajm]

Sounds like a Find Replace tool to me! Raw Data into the F anchor, Lookup table in the R anchor, and find on "Any Part of Field". You can use the append option to add this column - if there's a matching value, a value will come through. If there isn't, it'll be null - this is where a Filter can then filter out those rows you need / don't need.

[griffinwelsh · Accepted solution]

@knnwndlm see the attached solution

1. Use append Records to create a cartesian product

2. Use filter tool with formula Contains(trim(uppercase([Value])), trim(uppercase([TAX RATE LEVY])))

3. Summarize to eliminate duplicates.

[knnwndlm]

@alexnajm - That was my initial approach but I kept getting null value.