Fuzzy Lookup
FUZZYVLOOKUP
Searches the first column of a table and returns a selected column from the best fuzzy row.
Syntax
=FUZZYVLOOKUP(search_key, table_range, result_index, [threshold], [method], [case_sensitive], [return_score])Parameters
NameTypeDescription
Name
search_keyTypetext, number, or date
DescriptionValue to find.
Name
table_rangeTyperange
DescriptionTable whose first column contains candidate values.
Name
result_indexTypenumber
Description1-based column index to return from table_range.
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, and score. Defaults to FALSE.
Returns
The selected result column from the best row, or matched key/result/score when return_score is TRUE.
Example
=FUZZYVLOOKUP(A2, Accounts!A:D, 4, 0.82)Result: C-1042
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.