excel - Excel formula to check if items from a comma separated list in one cell exist in a comma separated list in another cell?

Question

I have a spreadsheet where every row has two columns, each containing a comma separated list of words or phrases.

Column 1                        | Column 2
---------------------------------------------------------
Orange, Pear, Sugar apple, Kiwi | Orange, Sugar apple
Banana, Watermelon, Pomegranate | Strawberry, Banana

I'm trying to create a formula that checks if the items listed in Column 2 are a subset of the items listed in Column 1 and outputs true or false.

In the above example the output should be true for Row 1 and false for Row 2.

The solutions I tried using the search and find functions only work if the items in Column 2 are listed in the same sequence, i.e. if Column 2 is a substring of Column 1.

标签： excelexcel-formula

Answer 1

使用这个数组公式：

=AND(ISNUMBER(SEARCH(", " & TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))-1)*99+1,99)) & ",",", "&A1&",")))

作为数组公式，退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 来确认