XLOOKUP vs Flookup Data Wrangler
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 match with confidence in a single formula.
The Problem With Exact Match Lookups
Your formula is correct. The arrays line up and the return range is right. Yet half the rows return #N/A.
Try
=XLOOKUP("Acme Corp", D2:D200, E2:E200)
against a list that contains "Acme Corporation". Excel and Google Sheets see two different strings and return nothing, even though a person would recognise the same company written two ways.
XLOOKUP fixes many of VLOOKUP's design problems, but it still has no notion of closeness. A string either equals the lookup value or it does not.
What XLOOKUP Does Well
XLOOKUP
searches a lookup range and returns a value from a return range that you choose independently.
=XLOOKUP(lookup_value, lookup_array, return_array, "Not found")It removes the two biggest frustrations of VLOOKUP. There is no column index to break when you insert a column and the return range can sit anywhere: to the left, to the right or on another sheet. It handles missing values with a clean default instead of a bare #N/A and it can return whole arrays rather than a single cell.
For clean data, XLOOKUP is the best built-in choice in modern Excel and Google Sheets. It is fast, readable and far less fragile than its predecessor. The limitation is that it still requires the strings to match exactly. Approximate mode exists, but it expects sorted numeric ranges, not name variants.
Where Exact Match Breaks
In real-world data, exact strings are rare. Common variants that return #N/A include:
| Variant | Example |
|---|---|
| Typos | "Jon Smith" versus "John Smith" |
| Abbreviations | "Acme Corp" versus "Acme Corporation" or "Robert" versus "Rob" |
| Word order | "Smith John" versus "John Smith" |
| Punctuation and spacing | "St. Johns Cafe" versus "St Johns Café" |
| Suffixes | "Microsoft" versus "Microsoft Inc." |
These are not edge cases. Exports from different systems, manual entry by different people and lists collected at different times produce them constantly.
Why Workarounds Fall Short
Three workarounds are commonly suggested and each helps only part of the way.
Wildcards
=XLOOKUP("*" & A2 & "*", table, return, "Not found")Wildcards find substrings but miss typos and create false positives when the substring appears elsewhere.
Helper Column Normalisation
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(A2," inc","")," corp","")))Normalisation removes casing, spacing and known suffixes. Apply it in both lists then XLOOKUP on the helper column. It improves match rate but misses spelling differences, word order changes and variants you did not strip explicitly.
Pattern Matching in Sheets
Sheets users often reach for FILTER with REGEXMATCH or QUERY with contains before turning to XLOOKUP. Both handle patterns well, yet neither returns a similarity percentage and complex regex becomes hard to maintain across many rows.
Bottom line. You can improve exact lookups, but you cannot make them fuzzy. For that you need a similarity score.
Flookup as a Fuzzy Alternative
Flookup scores how similar two strings are as a percentage and returns the best match with that score. Set a threshold and every pair above it is a match. Everything below is not.
Try Fuzzy Matching at 80 Percent Similarity
| Lookup Value | Best Match in Table | Score | Result at threshold |
|---|---|---|---|
| Global Tech Ltd | Global Tech Inc | 82% | Match |
| Acme Corp. | ACME CORPORATION | 88% | Match |
| Jennifer Walsh | Jen Walsh | 79% | No match |
| Nike Air Max | Nike Air | 84% | Match |
| 123 Main St | 123 Main Street | 90% | Match |
| Robert Johnson | Bob Johnson | 76% | No match |
Move the slider to see how the threshold changes which XLOOKUP style misses would still match. Mock data for illustration.
Two options:
Spreadsheet functions. In Google Sheets, type
=FLOOKUP(A2, D2:E100, 1, 2, 0.85, "score")
to search a column for the best match and return the result plus its score. The function runs inside Sheets and is ideal for live reconciliation.
Free browser tool. Paste two lists into the free fuzzy matching tool for XLOOKUP word order and abbreviations and get results without formulas. The tool works in Excel and Google Sheets workflows because it accepts CSV exports from either source.
New to fuzzy matching. See the step-by-step fuzzy match tutorial for threshold and mode guidance.
In Google Sheets there is no native fuzzy lookup at all. In Excel the free Fuzzy Lookup add-in is Windows-only and not available on Mac or on the web. The same similarity problem exists in both ecosystems.
XLOOKUP Compared With Flookup
| Aspect | XLOOKUP | Flookup |
|---|---|---|
| Match basis | Exact string, flexible array | Percentage similarity with threshold |
| Handles typos | No, returns #N/A | Yes, with scored confidence |
| Returns similarity | No | Yes, per row |
| Works on Mac and web | Yes | Yes, in Google Sheets and browser |
| Setup | Formula | Formula or one-click tool |
| Best for | Clean data with better error handling | Human-entered lists with variants |
When to Use Each Approach
Use XLOOKUP when the lookup value is a clean identifier that is guaranteed to be identical in both places, such as matching an order ID that was generated in one system and when you want the flexibility of independent lookup and return ranges.
Use Flookup when the same person or company appears with different spellings, such as vendor lists from two systems, CRM merges or customer surveys. You gain a confidence score for every match and avoid the false gap that exact lookups create.
Practical tip. Normalise first with trimming and standardisation and then apply fuzzy matching. The two steps together outperform either alone.