VLOOKUP vs Flookup Data Wrangler
Key Takeaways
- VLOOKUP and XLOOKUP require exact strings and return #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 VLOOKUP 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.
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 85 Percent Similarity
| Value A | Value B | Score | Result at threshold |
|---|---|---|---|
| Jon Smith | John Smith | 91% | Match |
| Acme Corp | Acme Corporation | 87% | Match |
| St Johns Cafe | St Johns Café | 95% | Match |
| Smith John | John Smith | 85% | Match |
| Robert | Rob | 78% | No match |
| Apple | 18% | No match |
Move the slider to see how the threshold changes which pairs count as a 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 VLOOKUP typos 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.
VLOOKUP Compared With Flookup
| Aspect | VLOOKUP | Flookup |
|---|---|---|
| Match basis | Exact string, column index | 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, exact data | Human-entered lists with variants |
When to Use Each Approach
Use VLOOKUP 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.