Excel - Filter column based on values from a different column

Question

I'm struggling with this for a while now. I'm not very familiar with Excel and I've searched on the internet to find a solution to this but unfortunately no success so far.

What I want to do is to create a new column based on these criteria. As you can see here

I want to create a new column based on column A but filtering out any cells that contain any of the words/set of words from column B. Finally, the new column will have cells that will not contain any of the words/set of words found in column B.

Is there any way of doing this?

Thank you!

question from:https://stackoverflow.com/questions/65898168/excel-filter-column-based-on-values-from-a-different-column

Answer 1

With Excel365 you can use combination of below dynamic formulas.

=LET(x,XLOOKUP("*" & INDEX(C1:C3,SEQUENCE(COUNTA(C1:C3))) &"*",A1:A5,A1:A5,,2),FILTER(x,NOT(ISERROR(x))))