XLOOKUP 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
- XLOOKUP matches on exact strings only, so typos, abbreviations and word order changes still return #N/A.
- Unlike VLOOKUP, XLOOKUP uses ranges instead of column indexes, looks in any direction and returns clean error messages.
- XLOOKUP returns arrays and works with spilled ranges, which makes it powerful for clean, well-structured data.
- Flookup scores similarity as a percentage, so Jon Smith and John Smith can be matched with confidence in one run.
What XLOOKUP Changed
XLOOKUP fixed the ergonomics of the lookup without changing what a lookup means. Four improvements matter in daily use:
- No column index: the return array is a range, so inserting a column no longer breaks every formula in the sheet or shifts every result to the right.
- A default for missing values: the if_not_found argument replaces the IFERROR wrapper that surrounded most VLOOKUPs.
- Match modes: exact, exact or next smaller, exact or next larger and wildcard, chosen with one argument instead of a boolean whose meaning was easy to forget.
- Search direction and arrays: the function can search from the last row backwards and can return a spill range, which makes it useful for top-n and last-match problems.
Those are real gains. A modern workbook is easier to maintain because of them.
What It Did Not Change
XLOOKUP still resolves a key by equality or by a pattern. It returns the first matching row in the chosen direction and one value from that row. It produces no similarity score, no ranking and no record of the candidates it rejected.
That is the boundary this comparison turns on. A better lookup is still a lookup, and the failure mode of a lookup is unchanged: when the key does not exist exactly, the function returns the not-found value and the reason never appears in the sheet.
Wildcards in Practice
The wildcard match mode accepts * for any sequence and ? for a single character, with ~ to escape a literal wildcard. It can find every value that starts with "Northwind" or contains "Trading", and for a fixed pattern that is genuinely enough.
Three limits appear as soon as the data is messy. The pattern is written per lookup, so a column with four variation types needs four formulas or a nested decision. The match is binary, so a value either satisfies the pattern or does not. And the function returns one row, so a value that matches several patterns produces a single answer with no evidence about the alternatives.
Where a Ranked Candidate List Wins
A review workflow needs three things a lookup does not provide: a score per candidate, more than one candidate when the data is ambiguous and a threshold that decides what reaches a person. That is the shape of a fuzzy match, and it is why a lookup and a matcher are not substitutes.
For a supplier list, an XLOOKUP answers "is this exact name present". A scored match answers "which three names are closest, and how close is each one", which is the question an accounts payable team actually has to resolve.
XLOOKUP Compared With Flookup
| Aspect | XLOOKUP | Flookup |
|---|---|---|
| Matching basis | Equality, or a wildcard pattern you write | Similarity score with a configurable threshold |
| Candidates | First match in the chosen direction | Ranked candidates with confidence badges |
| Missing key | Returns the if_not_found value | Returns the closest candidates, including low scores |
| Review | None, the formula returns a value | A review sheet with the score beside each pair |
| Word order and typos | Requires a helper column or a pattern per case | Handled by the similarity measure |
| Best at | Exact keys, last match, arrays, lookups across sheets | Names, addresses and exports without a stable key |
When the Lookup Is Enough
Use XLOOKUP when the identifier is reliable, when a wildcard expresses the variation, when the answer is a single value or when the workbook must not depend on an add-on. Use a scored match when the identifier is a human name, when the variations are unknown, when several candidates need comparison or when the output has to show why a pair was accepted.
The VLOOKUP comparison covers the older function and the helper-column workarounds that still apply, and the algorithm guide explains the edit-distance arithmetic behind a similarity score.