PrecisionCalc
inspector text
Do Anything with Text in Formulas

 

itISFUZZYMATCH

Compares two text strings and determines whether they are alike enough to meet a minimum fuzzy match score.

 

Syntax

itISFUZZYMATCH(fuzzy_string_1,fuzzy_string_2,,case_sensitive,min_match_strength,min_match_strength_type,include_out_of_order_matches,out_of_order_match_strength)

fuzzy_string_1 Required. One of the two strings to compare.
fuzzy_string_2 Required. The other of the two strings to compare.
case_sensitive Optional. Determines whether to distinguish between lower case and upper case. Set to False to ignore case differences. True by default.
min_match_strength Optional. The minimum fuzzy match score the two text strings must meet for itISFUZZYMATCH to return True. Must be a number between 0 and 1.

0.67 by default.

For details on how the fuzzy match score is determined, see itFUZZYCOMPARE.
min_match_strength_type Optional. Determines how the fuzzy match score is determined. 1 by default.

 

min_match_strength_type value

Result
1 The fuzzy match score is the percent of the length of the match to the longer input string's length.

If the input strings are 8 characters long and 12 characters long, and the match found is 6 characters long, the return value is 6/12 = 0.5.

If include_out_of_order_matches is True or omitted, out of order matches may increase the fuzzy match score. See include_out_of_order_matches.

If min_match_strength_type is omitted, this is the default.
2 The fuzzy match score is the percent of the length of the match to the shorter input string's length.

If the input strings are 8 characters long and 12 characters long, and the match found is 6 characters long, the return value is 6/8 = 0.75.

If include_out_of_order_matches is True or omitted, out of order matches may increase the fuzzy match score. See include_out_of_order_matches.
3 The fuzzy match score is the percent of the length of the match to the average of the two input strings' lengths.

If the input strings are 8 characters long and 12 characters long, and the match found is 6 characters long, the return value is 6/((8+12)/2) = 6/(20/2) = 6/10 = 0.6.

If include_out_of_order_matches is True or omitted, out of order matches may increase the fuzzy match score. See include_out_of_order_matches.
include_out_of_order_matches Optional. itISFUZZYMATCH makes one pass through the two original input strings, finding matches. Then, by default, it makes more passes, assigning each successive pass a lower fuzzy match score factor (see out_of_order_match_strength), making as many passes as needed before there are no more characters in common.

You may prefer to restrict the search to a single pass only. To do so, set include_out_of_order_matches to False.

Consider the input strings abbccee and eebbccf. The first pass finds bbcc. The second pass finds ee. The second pass' contribution to the final matching score is reduced by out_of_order_match_strength. Then there are no remaining matching characters.

True by default.
out_of_order_match_strength Optional. If specified, must be a number from 0 to 1.

0.75 by default.

If include_out_of_order_matches is True or omitted, itISFUZZYMATCH makes multiple passes searching for matching characters, assigning each successive pass a lower fuzzy match score factor, making as many passes as needed before there are no more characters in common.

To make each successive pass' match count less toward the final score per matching character than the pass before it, each is multiplied by out_of_order_match_strength, and out_of_order_match_strength is itself multiplied by the previous pass' out_of_order_match_strength.

For example, if 5 passes are required before all matching characters are found:

  • The first pass counts in full toward the final score. out_of_order_match_strength is effectively 1 for the first pass. If, for example, the first pass has a fuzzy match score of 0.6, then the entire 0.6 counts toward the final score.
  • The second pass' score is reduced by multiplying it by out_of_order_match_strength. If out_of_order_match_strength is 0.75 (the default), and if the second pass has a raw score of 0.2, then the second pass contributes 0.2 x 0.75 = 0.15 to the final score.
  • The third pass' out_of_order_match_strength is reduced by multiplying it by the previous pass' out_of_order_match_strength, for an effective out_of_order_match_strength of (by default) 0.75 x 0.75 = 0.5625. If the third pass has a raw score of 0.1, then the third pass contributes 0.1 x 0.5625 = 0.05625 to the final score.
  • The fourth pass has an effective out_of_order_match_strength of (by default) 0.5625 x 0.75 = 0.421875.
  • The fifth pass has an effective out_of_order_match_strength of (by default) 0.421875 x 0.75 = 0.316406.

 

Examples

Formula Description Result
=itISFUZZYMATCH("abc","abc") Determine whether "abc" and "abc" are alike enough to meet the default minimum fuzzy matching score of 67%. TRUE
=itISFUZZYMATCH("abc","bcd") Determine whether "abc" and "bcd" are alike enough to meet the default minimum fuzzy matching score of 67%. TRUE
=itISFUZZYMATCH("abc","bcd") Determine whether "abc" and "cde" are alike enough to meet the default minimum fuzzy matching score of 67%. FALSE
=itISFUZZYMATCH("ABC","abc") Determine whether "ABC" and "abc" are alike enough to meet the default minimum fuzzy matching score of 67%. FALSE
(itISFUZZYMATCH is case sensitive by default.)
=itISFUZZYMATCH("ABC","abc",FALSE) Determine whether "ABC" and "abc" are alike enough (in a non-case sensitive comparison) to meet the default minimum fuzzy matching score of 67%. TRUE
=itISFUZZYMATCH("abc","bcd",,0.9) Determine whether "abc" and "bcd" are alike enough to meet the customized minimum fuzzy matching score of 90%. FALSE
=itISFUZZYMATCH("abcdefgh","cdefghijklmn",,,1) Determine whether "abcdefghi" and "cdefghijklmn" are alike enough to meet the default minimum fuzzy matching score (the percentage of the fuzzy match length to the longer of the two inputs) of 67%. FALSE
(The matching string is "cdefgh", which is 6 characters long. The longer of the two input strings, "cdefjhijklmn", is 12 characters long. 6/12 = 50%.)
=itISFUZZYMATCH("abcdefgh","cdefghijklmn",,,2) Determine whether "abcdefghi" and "cdefghijklmn" are alike enough to meet the minimum fuzzy default matching score (the percentage of the fuzzy match length to the shorter of the two inputs) of 67%. TRUE
(The matching string is "cdefgh", which is 6 characters long. The shorter of the two input strings, "abcdefjh", is 8 characters long. 6/8 = 75%.)
=itISFUZZYMATCH("abcdefgh","cdefghijklmn",,,3) Determine whether "abcdefghi" and "cdefghijklmn" are alike enough to meet the default minimum fuzzy matching score (the percentage of the fuzzy match length to the average of the two inputs) of 67%. FALSE
(The matching string is "cdefgh", which is 6 characters long. The average length of the two inputs is (8+12)/2 = 10. 6/10 = 60%.)
=itISFUZZYMATCH("abc123","123abc") Determine whether "abc123" and "123abc" are alike enough (allowing out of order matches to be included by default) to meet the default minimum fuzzy matching score of 67%. TRUE
(The first pass results in a score of 3/6 = 50%. The second pass, picking up out of order matches, results in a score of (3/6) x 0.75 = 37.5%. 50% + 37.5% = 87.5%.)
=itISFUZZYMATCH("abc123","123abc",,,,FALSE) Determine whether "abc123" and "123abc" are alike enough (excluding out of order matches) to meet the default minimum fuzzy matching score of 67%. FALSE
(The first pass results in a score of 3/6 = 50%. No additional passes are performed; out of order matches are ignored.)
=itISFUZZYMATCH("abc123","123abc",,,,,0.25) Determine whether "abc123" and "123abc" are alike enough, with a custom out of order match strength of 0.25, to meet the default minimum fuzzy matching score of 67%. FALSE
(The first pass results in a score of 3/6 = 50%. The second pass results in a score of (3/6) x 0.25 = 12.5%. 50% + 12.5% = 62.5%.)
=itISFUZZYMATCH(A1,A2,A3,A4,A5)
 
  A
1  abc123
2  123bcd
3  TRUE
4  0.67
5  1
6  TRUE
7  0.75
Determine whether "abc123" and "123abc" are alike enough to meet the minimum fuzzy matching score of 67%. TRUE

 

 

PrecisionCalc Home Page