What is the FLOOKUP function
FLOOKUP lets you find the closest text match in a list, even when there are typos, abbreviations, or variations in spelling.
It searches a range of text values and returns the best match, using either substring or Levenshtein distance matching.
This is useful when your data isn’t perfectly consistent — for example, when company names or product titles vary slightly.
How does the FLOOKUP function work
1FLOOKUP(search_value, search_range, [type], [threshold])| Parameter | Description |
|---|---|
| search_value | The text or cell reference containing the string to match. Example: "ACME LTD" or A2. |
| search_range | The one-dimensional range of candidate strings to search within. Example: B2:B100. |
| [type] (optional) | Matching algorithm selector: • 1 (default) – Case-insensitive substring matching. • 2 – Levenshtein distance matching (fuzzy edit-distance match). |
| [threshold] (optional) | Used only with type 2. Sets the maximum allowed edit distance. Example: 2 allows up to two edits (insertions, deletions, or substitutions). |
Use cases
Find a company name using substring match
1=FLOOKUP("Wayne", A2:A5, 1)If the list in A2:A5 contains "Wayne Enterprises", the function returns "Wayne Enterprises" because "Wayne" appears as a substring.
Match names with small typos using Levenshtein distance
1=FLOOKUP("Globx", A2:A5, 2)If the list contains "Globex", the formula returns "Globex" — even though there’s a missing “e” — because it’s within an acceptable edit distance.
Limit the match distance for stricter accuracy
1=FLOOKUP("ACME LTD", A2:A4, 2, 1)Here, the search value "ACME LTD" won’t match "ACME LIMITED" because the difference exceeds the threshold of 1.
Troubleshooting
| Issue | Possible cause and solution |
|---|---|
#N/A error | No match found. Try switching to Levenshtein mode (type=2), increasing the threshold, or checking for extra spaces in your data. |
| Unexpected match | When using substring mode (type=1), FLOOKUP returns the first partial match it finds. If there are duplicates or similar entries, reorder your list to prioritize the best matches. |
| Wrong range size | search_range must be a single row or column. Multi-column ranges aren’t supported. |
| Slow performance | Levenshtein matching (type=2) is slower on large datasets. Use substring mode (type=1) whenever possible. |
Best Practices
- Clean your data – Remove extra spaces, unify casing, and fix common typos before running fuzzy lookups.
- Use thresholds wisely – Smaller thresholds (e.g.
1or2) ensure results stay relevant and prevent poor matches. - Test both modes – Try substring (
1) first for speed, and switch to Levenshtein (2) only when necessary. - Keep lists ordered – Since
FLOOKUPreturns the first match found, put the most relevant items near the top.
By using FLOOKUP, you can handle real-world, imperfect text data more effectively in your Rows spreadsheets — helping you automate matching tasks that would otherwise require manual cleanup.