VLOOKUP vs Flookup for Fuzzy Name Matching
About the author
Andrew Apell is the creator of Flookup Data Wrangler and has built data cleaning tools for Google Sheets since 2018. The method in this post reflects work on large reconciliation tasks with a government team and continued testing with Sheets users.
Every Flookup result described here can be checked in the documentation before purchase.
Read
Our Story
for the background, or
contact the team
with any questions about this post, including corrections, unclear steps or use cases we have not covered yet.
Key Takeaways
- VLOOKUP matches on the first column of a range and returns a value from the same row, but only when the strings are identical.
- Because it uses a column index, VLOOKUP breaks when columns are inserted, moved or deleted and it can only look to the right.
- Wildcard and helper-column tricks help with simple variants but still miss typos, word order changes and suffixes you did not list.
- Flookup adds a percentage similarity score, so Jon Smith and John Smith can be matched with a confidence value in one run.
What a Lookup Actually Promises
VLOOKUP answers one question: does this exact key exist in that column, and if it does, what is in the column beside it. Every behaviour follows from that contract. The lookup compares a value for equality, returns the first row that matches and gives no opinion about how close a non-matching row came.
The approximate form of the function, where the fourth argument is TRUE or omitted, does not soften the contract. It assumes the first column is sorted ascending and returns the largest value less than or equal to the lookup value, which is the right tool for tax bands and grade thresholds. It is not a similarity measure, and a sorted list of company names read in approximate mode returns a neighbour rather than a match.
That distinction is where most comparisons go wrong. The question is not whether VLOOKUP is accurate. It is whether the thing you are looking up has a reliable key.
Three Ways an Exact Key Goes Wrong
Three variations account for most failed lookups, and none of them is an error in the formula:
- A typo: "Jonson" against "Johnson" differs by one character, so the key is different and the lookup returns nothing.
- A reordered value: "Smith, John" against "John Smith" contains the same words in a different sequence, which is a different key.
- An abbreviation or suffix: "Acme Corp" against "Acme Corporation" is the same company and a different string.
In each case the formula is working exactly as designed. The list is the problem, because it never had a stable key in the first place.
Why Helper Columns Only Move the Problem
The standard remedy is a chain of helpers: TRIM the spaces, UPPER the case, SUBSTITUTE the suffixes, then look up the cleaned value. That works, and it is the correct approach when you know every variant in advance.
The limit is the word "every". A helper column encodes the variants someone thought of. The next export adds a fourth spelling, the cleaned keys stop matching and the failure looks identical to the original problem. Wildcards move the boundary a little further, because * and ? can express a prefix or a contains pattern, but each wildcard is a pattern you must write per lookup and it still returns the first hit rather than the best one.
The Test: Can You Write the Rule?
Before choosing a tool, try to write the rule that turns a non-matching value into a matching one. If the rule fits in a helper column, a formula is the right answer and no add-on is needed. If the rule is "anything close enough to review", the rule is a threshold over a score, and a lookup cannot express it.
That is the whole decision. Exact keys belong in formulas. Ranked similarity belongs in a matcher, whether that is a spreadsheet function or a script.
VLOOKUP Compared With Flookup
| Aspect | VLOOKUP | Flookup |
|---|---|---|
| Input assumption | The key is identical in both tables | The values are similar, with a threshold for how similar |
| Output | One value per lookup, or an error | A matched value with a similarity score beside it |
| Borderline cases | Not represented at all | Held in a review band for a person to decide |
| Variants it handles | Those covered by helper columns and wildcards | Typos, word order, punctuation and phonetic variants |
| Where it runs | Excel and Google Sheets, no add-on | Google Sheets with the Flookup add-on |
| Best at | Exact keys, coded identifiers, small lookups | Names, companies, addresses and messy exports |
When a Formula Is the Right Answer
Keep the formula when the key is genuinely an identifier, when the variants are known and finite, when the list is small enough to check by eye or when the workbook has to work without an add-on. Those are common conditions, and a fuzzy tool used against a clean key is unnecessary work.
Move to a scored match when the key is a name or an address, when the variants are unknown, when someone has to approve the borderline rows or when the output needs a confidence value rather than a single cell. The name matching guide covers the pair types that make an exact key unreliable, and the XLOOKUP comparison covers what the newer function changes about this picture.