Fuzzy Lookup
FUZZYMATCH
Returns the 1-based position of the closest fuzzy match in a range.
Syntax
=FUZZYMATCH(search_key, lookup_range, [threshold], [method], [case_sensitive])Parameters
NameTypeDescription
Name
search_keyTypetext, number, or date
DescriptionValue to find.
Name
lookup_rangeTyperange
DescriptionCandidate values to search.
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.
Returns
The 1-based candidate position, blank for a blank search key, or #N/A when no candidate reaches threshold.
Example
=FUZZYMATCH("Jilian", A2:A20, 0.9)Result: 2
Returns the position of the closest candidate in A2:A20.
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.