Fuzzy Lookup
FUZZYLOOKUP
Searches one range fuzzily and returns the matching value or row from a separate result range.
Syntax
=FUZZYLOOKUP(search_key, lookup_range, result_range, [threshold], [method], [case_sensitive], [return_score])Parameters
NameTypeDescription
Name
search_keyTypetext, number, or date
DescriptionValue to find.
Name
lookup_rangeTyperange
DescriptionCandidate values to search.
Name
result_rangeTyperange
DescriptionValues or rows to return from the same matching position.
Name
threshold?Typenumber or percent text
DescriptionMinimum score required. Defaults to 0.8.
Name
method?Typetext
DescriptionMatching method. Defaults to jaro_winkler.
Name
case_sensitive?Typeboolean
DescriptionTRUE preserves case. Defaults to FALSE.
Name
return_score?Typeboolean
DescriptionTRUE spills matched key, returned value or row, and score. Defaults to FALSE.
Returns
The matched result cell or row, or matched key/result/score when return_score is TRUE.
Example
=FUZZYLOOKUP(A2, Accounts!A:A, Accounts!B:B, 0.82, "token_sort")Result: West Acme LLC
Notes
SIMILARITY uses "jaro_winkler" by default because it works well for names and short labels.
Supported methods are "jaro_winkler", "jaro", "levenshtein", "token_sort", and "token_set".
Text is compared case-insensitively by default, with whitespace collapsed and diacritics normalized.
The default threshold is 0.8. You can pass decimals such as 0.86 or percents such as 86%.
When return_score is TRUE, lookup functions spill the matched key, result value or row, and score for review.