Keys, Duplicates and Joining Two Files

A key is a promise

When you join a price list to a sales file, you are promising that the article code identifies exactly one row in the price list. If it identifies two, your join invents rows or picks one arbitrarily; if it identifies none, you get #N/A. Everything about joining data is really about checking that promise before you rely on it.

Check for duplicates before joining, not after

One formula settles it. Add a helper column beside the key and read the maximum:

=COUNTIF($A$2:$A$200,A2)      ' 1 = unique, 2+ = duplicate key
=SUMPRODUCT(--(COUNTIF(A2:A200,A2:A200)>1))   ' how many rows have a duplicate key

If any value comes back above 1, decide deliberately what a duplicate means here — a genuine second record, a re-entry, or two branches sharing a code — before writing a single lookup. VLOOKUP will not warn you: it returns the first match it finds, in sheet order, which changes if anybody sorts the file.

The exact-match argument is not optional

VLOOKUP's fourth argument defaults to approximate matching, which requires the lookup column to be sorted ascending and otherwise returns confidently wrong values rather than an error. Always write FALSE (or 0). XLOOKUP reversed this default and matches exactly unless told otherwise, which is the main reason to prefer it where it is available:

=VLOOKUP("A-104",$A$2:$C$15,3,FALSE)
=XLOOKUP("A-104",$A$2:$A$15,$C$2:$C$15,"not found")
=INDEX($C$2:$C$15,MATCH("A-104",$A$2:$A$15,0))

INDEX/MATCH is the version that works in every Excel version, looks left as well as right, and does not break when somebody inserts a column — VLOOKUP's column number is a position, and inserting a column silently shifts what it points at.

Why keys fail to match when they look identical

• Type mismatch — 104 as a number in one file, "104" as text in the other. The most common cause of #N/A, and invisible on screen.
• Trailing spaces — "A-104 " from a system export. TRIM on both sides, and LEN to prove it worked.
• Case and accents — Excel's lookups ignore case but not accents, so Geneve and Genève are two different keys.
• Invisible characters — non-breaking spaces and line breaks from copied web content.
• Near-duplicate labels — Zurich, Zürich and ZH are three cantons as far as any formula is concerned.

Wrap the lookup, but not too early

=IFERROR(VLOOKUP(...),"") makes a report look clean, and that is exactly the risk: it hides genuine mismatches. Build the join with the errors visible, count them (=COUNTIF(D2:D200,"#N/A") will not work on error values, so use =SUMPRODUCT(--ISNA(D2:D200))), understand why each one failed, and only then suppress what remains.