How to Calculate Percentage Similarity in Google Sheets
Summary of Steps
Type =FUZZYMATCH inside any cell.
Enter any 2 strings or ranges as the first and second arguments.
Press Return or Enter to see the result.
You can use FUZZYMATCH to calculate the percentage similarity between any two strings in Google Sheets, especially strings that follow name-like patterns. FUZZYMATCH allows you to compare strings in three modes: string vs string, string vs list and list vs list. The last two modes cut down the time taken to calculate percentage similarities from minutes to mere seconds by bulk processing multiple rows at once.
The following example shows how to use FUZZYMATCH in string vs string mode:
The formula shown in the image above works through one row at a time and yields the following results:
This formula is sufficient for most cases, but sometimes you need more power and efficiency. In such cases, especially if you are working with long lists, using FUZZYMATCH in the list vs list mode is the better option.
Insert this formula =FUZZYMATCH(A2:A15,B2:B15) inside cell B2.
In this mode, FUZZYMATCH works through all the rows in one fell swoop and returns results exactly the same as the first mode. The key difference here is that, whereas the string vs string might take minutes to complete long lists, this list vs list mode completes the same work in seconds.
Finally, in some cases, you might simply want to compare one string to multiple strings. To accomplish this, we use the third mode of FUZZYMATCH, and that is string vs list.
Say you have just one string of interest in column A and a list of strings in column B. In order to compare the two columns, we can use a formula like this:
This formula will return an array of percentage similarities between the first and each item in the second argument.
That concludes our text comparison tutorial. Be sure to check out the Documentation page for details about all Flookup functions.