VLOOKUP vs Flookup for Fuzzy Name Matching

About the Author

Andrew Apell has built data cleaning tools for Google Sheets since 2018. Read Our Story or contact the team with any questions. The documentation covers each Flookup feature described here.


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 be matched with a confidence value in one run.

What a Lookup Actually Promises

VLOOKUP answers one question: does this exact key exist in that column and if it does, what is in the column beside it. Every behaviour follows from that contract. The lookup compares a value for equality, returns the first row that matches and gives no opinion about how close a non-matching row came.

The approximate form of the function, where the fourth argument is TRUE or omitted, does not soften the contract. It assumes the first column is sorted ascending and returns the largest value less than or equal to the lookup value, which is the right tool for tax bands and grade thresholds. It is not a similarity measure and a sorted list of company names read in approximate mode returns a neighbour rather than a match.

That distinction is where most comparisons go wrong. The question is not whether VLOOKUP is accurate. It is whether the thing you are looking up has a reliable key.


Three Ways an Exact Key Goes Wrong

Three variations account for most failed lookups and none of them is an error in the formula:

In each case the formula is working exactly as designed. The list is the problem, because it never had a stable key in the first place.


Why Helper Columns Only Move the Problem

The standard remedy is a chain of helpers: TRIM the spaces, UPPER the case, SUBSTITUTE the suffixes, then look up the cleaned value. That works and it is the correct approach when you know every variant in advance.

The limit is the word "every". A helper column encodes the variants someone thought of. The next export adds a fourth spelling, the cleaned keys stop matching and the failure looks identical to the original problem. Wildcards move the boundary a little further, because * and ? can express a prefix or a contains pattern, but each wildcard is a pattern you must write per lookup and it still returns the first hit rather than the best one.


The Test: Can You Write the Rule?

Before choosing a tool, try to write the rule that turns a non-matching value into a matching one. If the rule fits in a helper column, a formula is the right answer and no add-on is needed. If the rule is "anything close enough to review", the rule is a threshold over a score and a lookup cannot express it.

That is the whole decision. Exact keys belong in formulas. Ranked similarity belongs in a matcher, whether that is a spreadsheet function or a script.


VLOOKUP Compared with Flookup

Aspect VLOOKUP Flookup
Input assumption The key is identical in both tables The values are similar, with a threshold for how similar
Output One value per lookup or an error A matched value with a similarity score beside it
Borderline cases Not represented at all Held in a review band for a person to decide
Variants it handles Those covered by helper columns and wildcards Typos, word order, punctuation and phonetic variants
Where it runs Excel and Google Sheets, no add-on Google Sheets with the Flookup add-on
Best at Exact keys, coded identifiers, small lookups Names, companies, addresses and messy exports

When a Formula Is the Right Answer

Keep the formula when the key is genuinely an identifier, when the variants are known and finite, when the list is small enough to check by eye or when the workbook has to work without an add-on. Those are common conditions and a fuzzy tool used against a clean key is unnecessary work.

Move to a scored match when the key is a name or an address, when the variants are unknown, when someone has to approve the borderline rows or when the output needs a confidence value rather than a single cell. The name matching guide covers the pair types that make an exact key unreliable and the XLOOKUP comparison covers what the newer function changes about this picture.


Frequently Asked Questions

Is there a fuzzy VLOOKUP in Excel or Google Sheets?

Neither product includes a built-in fuzzy VLOOKUP. VLOOKUP, XLOOKUP and INDEX with MATCH work from exact keys or a wildcard pattern you write, not a similarity score. Fuzzy matching needs a similarity engine such as Flookup, which scores how close two strings are and returns the best match with a percentage.

Why does my VLOOKUP return #N/A even though the value looks the same?

A single extra space, different capitalisation, a missing period or an abbreviation such as Corp versus Corporation is enough for VLOOKUP to return #N/A. Trimming and lower-casing help, but typos and word order changes still fail on exact match.

Why does VLOOKUP return the wrong value after I insert a column?

VLOOKUP reads the column by its index number, not by its header. If you insert a column to the left of the return column, every formula shifts to a new field. Use INDEX with MATCH, XLOOKUP or a fuzzy engine to avoid index-based lookups.

Can VLOOKUP do approximate matching on text such as names?

Approximate mode in VLOOKUP expects sorted numeric ranges, not name variants. Using wildcards helps with simple contains checks but misses typos and produces false positives.
Text similarity requires a dedicated fuzzy engine.

Does Google Sheets have a fuzzy lookup function?

Google Sheets has no native fuzzy lookup. Flookup adds fuzzy matching directly inside Sheets with Flookup custom functions such as FLOOKUP and SHEETMERGE, so nothing depends on an exact string.


You Might Also Like