Ever wanted to compare two sentences irrespective of the sequence of words in it. Let me code a clever Google Sheets formula for you to compare two strings irrespective of the word positions in it. I hope this could be useful for some of you.

Comparing two strings are pretty easy if the word positions are the same. With a simple Google Sheets formula, you can test it as follows.

Assume the cells A1 and B1 contain the same phrase “Brazil and Canada”. Then, just type the below formula in cell C1. It will return TRUE since both the values match.

=A1=B1

What if cell B1 contains “Canada and Brazil”? No doubt, the above formula would return FALSE.

If you check word by word, actually both the strings are the same. But the word sequence is not the same. That’s why the above formula returns FALSE. I want a formula to return TRUE in this case.

So I am going to code a formula that compares two strings irrespective of the word positions in Google Sheets.

To achieve this, I am using a combination of SPLIT and SEARCH functions. See how to code that cool formula below.

Google Sheets Formula to Compare Two Strings Irrespective of the word Positions

To explain how to code formula for this purpose, I am taking the sample data provided on the screenshot above.

So my formula will be in cell D1 in that Sheet. To simplify and help you to understand, I am going to provide you a non-array formula.

It can compare the two strings in a single row. That means to include additional rows, you must drag the fill handle of the cell containing the formula down.

I do have an array formula too. That I will provide at the end of this tutorial. You can use that out of the box. So here we go.

Non-Array Formula that Compares Two Strings Ignoring Word Sequence

Step 1:

In this step, I am going to Split the string in cell C1. So it will return the following words (see the image).

=split(C1," ")

Step 2:

The formula returns three strings. We can now check whether these three strings are available in cell B1.

We are checking multiple strings. So we must use the ArrayFormula function with the Search.

=ArrayFormula(search(split(C1," "),B1))

Since all the three search keys (strings) are available in cell B1, the formula would return 3 numeric numbers representing the position of each string. If any mismatch, the formula would return the #VALUE! error.

From this, we can make sure of one thing. If all the words in the sentence/phrase/string in cell C1 are existing in the string in cell B1, there won’t be any single error in the output.

Now we must do the same testing with the string in cell B1.

Step 3:

I hope you have learned the two formulas above. So here I am directly providing you the Search and Split combination.

=ArrayFormula(search(split(B1," "),C1))

With this formula, similar to the earlier example, we can ensure whether all the words in cell B1 are existing in cell C1 or not.

Step 4:

Combine both the Step 2 and 3 formulas as below.

This formula compares two strings irrespective of the word positions in Google Sheets. If there is a mismatch, the formula would return at least one #VALUE! error.

So we can finally use the above formula as below to return TRUE for match and FALSE for the mismatch.

Step 5:

Using the IFERROR function with the above formula, we can replace all the #VALUE! errors with the choice of value that we want.

The idea here is like this. The number that we are using to replace the errors must be greater than the total of the search formula output. I am replacing the errors with the value 1000. Why?

First, see the below formula. Then I’ll try to make you understand the above number logic.

=ArrayFormula(IFERROR({search(split(C1," "),B1),search(split(B1," "),C1)},1000))

In the next step, I will SUM the output. If the total of the values is greater than or equal to 1000, that means there is at least one mismatch. That is the logic behind using the number 1000 to replace error values in the search output.

I can use IF logical test (this or that) to return TRUE/FALSE based on the output.

Final Formula:

You can use this formula in cell D1 and copy down to compare strings for a match without considering the word positions.

=if(sum(ArrayFormula(IFERROR({search(split(C1," "),B1),search(split(B1," "),C1)},1000)))>=100,FALSE,TRUE)

Array Formula that Compares Two Strings Ignoring Word Sequence

I have converted the above formula to an array formula. So you can just enter it in cell D1 and leave it to expand.

=ArrayFormula(if(len(B1:B),(IF(MMULT(IFERROR(IF(LEN(B1:B),{search(split(C1:C," "),B1:B),search(split(B1:B," "),C1:C)},0),10000),SIGN(ROW(INDIRECT("A1:A"&COLUMNS(ArrayFormula(IFERROR(IF(LEN(B1:B),{search(split(C1:C," "),B1:B),search(split(B1:B," "),C1:C)},),99999)))))))>=10000,FALSE,TRUE)),))

Here also I have used the same logic. Since multiple rows are involved, I have used the MMULT to sum values in each row instead of the SUM function. That makes it a little tough to read.

I am not attempting to explain this formula but you can use it out of the box. Change C1:C and B1:B in this formula to the columns that contain your strings to compare. Leave A1:A in the formula as it is.

That’s all. Hope you have enjoyed the stay!