VLOOKUP vs Flookup Data Wrangler
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 match with a confidence value in a single formula.
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". Excel sees two different strings and returns nothing, even though a person would spot the same name written two ways instantly.
This is the core limitation of exact match: it has no sense of closeness. A string either equals the lookup value or it does not and there is no score in between.
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 in Excel.
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)Its strengths are speed and familiarity. It is fast on clean data, easy to read and every spreadsheet user recognises it. It is the right tool when the lookup value is an identifier such as an order number, a SKU or a product code that was generated by a single system.
Its weaknesses are structural. The column index number makes formulas fragile: insert a column and every formula to its right silently starts returning the wrong field. VLOOKUP can only look left, so the return column must sit to the right of the match column. And because the index is a number, it is hard to read a year later and hard to audit when it breaks.
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. They also slow down large tables because they stop exact-match indexing.
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. Each new variant you discover means editing the formula again.
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. It is designed for lookup tables such as tax bands, not names.
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 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 your table layout will not change.
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.