XLOOKUP vs Flookup Data Wrangler
Key Takeaways
- XLOOKUP requires exact strings and returns #N/A on typos, abbreviations and word order changes.
- Wildcards and helper-column normalisation improve exact lookups but miss many real-world variants.
- Flookup scores similarity as a percentage and returns the best match, so Jon Smith and John Smith can match with confidence.
- Use built-in lookups for clean, exact data and Flookup for human-entered lists that were never standardised.
The Problem With Exact Match Lookups
Your formula is correct. The columns line up and the range is right. Yet half the rows return #N/A.
Try
=VLOOKUP("Jon Smith", A2:B100, 2, FALSE)
against a list that contains "John Smith". Or
=XLOOKUP("Acme Corp", D2:D200, E2:E200)
against "Acme Corporation". Excel and Google Sheets see two different strings and return nothing. Humans see the same person and the same company written two ways.
That gap between what you see and what the lookup sees is the fundamental limitation of exact match.
What XLOOKUP Does Well
VLOOKUP
searches the first column of a range and returns a value from the same row. It has served spreadsheets for decades and remains the most taught lookup.
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
XLOOKUP
improves on it. It removes column index fragility, looks left or right, handles missing values cleanly and works in both Excel and Google Sheets.
=XLOOKUP(lookup_value, lookup_array, return_array, "Not found")Both are excellent when the lookup value and the table value are identical after normalisation. They are fast, reliable and well understood. Approximate mode in VLOOKUP 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
=VLOOKUP("*" & A2 & "*", table, 2, FALSE)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 files then VLOOKUP on the helper column. It improves match rate but misses spelling differences, word order changes and variants you did not strip explicitly.
Approximate Mode
=VLOOKUP(A2, table, 2, TRUE)Approximate mode expects sorted numbers, not text similarity. On text it walks the sorted list and returns the wrong row, without scoring how close the match is.
Bottom line. You can improve exact lookups, but you cannot make them fuzzy. For that you need a similarity score.
Sheets users often try FILTER with REGEXMATCH or QUERY with contains before turning to XLOOKUP. Both handle patterns, yet neither returns a similarity percentage like FLOOKUP does.
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 guaranteed to be identical in both places, such as matching an order ID that was generated in one system.
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.