How do I check if every word in a cell is in another cell, regardless of word order?

If you have a cell with a text string in it, and want to check if every word in that string is contained in another cell, regardless of word order, how would you go about doing that?

Example:

If you have Microsoft-365 then could try-

=IF(AND(ISNUMBER(SEARCH(TEXTSPLIT(A2,," "),B2))),"Match","No Match")

To check if every word in a cell appears in another cell, regardless of word order, use the following formula:

<code>=IF(PRODUCT(ISNUMBER(SEARCH(TEXTSPLIT([@Cell1]," "),[@Cell2]))+0),"MATCH","NO MATCH")</code>

The above formula assumes Cell1 has all the words you want to search for, the words are separated by a space, and Cell2 is where you'll be searching. If the words are separated by something else, like a comma, the TEXTSPLIT portion would look like this instead:

<code>TEXTSPLIT([@Cell1],",")</code>

The formula works by:

1. Splitting the string by a delimiter - with TEXTSPLIT
2. Searching for each portion in the other cell - with SEARCH
3. Checking if this returns a number - with ISNUMBER (SEARCH returns a position number)
4. Adding zero to turn true/false into 1/0
5. Multiplying the results to only get a 1 if all terms are found - with PRODUCT
6. Returning "MATCH" if true (1) or "NO MATCH" if false (0) using an IF statement

IMPORTANT: This will match if words are contained within other words. For instance, if Cell1 contains "Apple Bicycle" and Cell2 contains "Bicycle Tree Pineapple", this will return "MATCH". (Per Jos Woolley)

To ignore words contained within other words, you can use the following:

<code>=IF(AND(ISNUMBER(XMATCH(TEXTSPLIT(A2," "),TEXTSPLIT(B2," ")))),"MATCH","NO MATCH")</code> (Per harun24hr)