How to merge two Excel files by a common column
- Choose file A, the one you want to complete (orders, employees, a customer list), and file B, the one that holds the extra data (prices, departments, regions).
- Check the key. The column the two files have in common, such as an ID, SKU or email, is suggested automatically, even if it has a different name in each file. Add a second column for composite keys.
- Pick the columns to bring from B and which rows to keep.
- Merge and download. The Excel file has the result, the rows of A that were not found in B, the rows of B that were not found in A, and a summary.
Private by design
Both files are read and matched by JavaScript in your browser, in a background worker. They are never uploaded, so you can match payroll, customer or supplier data safely. The page only downloads its own code and the open-source SheetJS and PapaParse libraries from this site.
A VLOOKUP for whole files
In Excel, bringing a column from another workbook means writing =VLOOKUP(A2,[prices.xlsx]Sheet1!$A:$C,3,FALSE) or =XLOOKUP(A2,…), copying it down, repeating it for every column you need, and then fighting with #N/A. Here you choose the key once, tick the columns, and every row gets its data. The result is plain values, so it does not break when the lookup file is moved or closed.
It also does what VLOOKUP cannot: match on two or three columns at once, show which rows found nothing in either file, and return every match instead of only the first one when a key is repeated.
Which rows end up in the result
- All of A (left join)The default and the VLOOKUP behaviour: every row of A, with B’s columns filled where the key matches and empty where it does not.
- Only matches (inner join)Only the rows whose key exists in both files. Useful to extract the customers that placed an order, or the products that are in both catalogues.
- All of A and B (full outer join)Every row of both files. Rows found in only one file are marked “A only” or “B only”, and the key column is filled from whichever file has it.
- All of B (right join)The mirror of the left join: every row of B, completed with A’s columns.
Duplicate keys: first match or all matches
If the same key appears several times in B, VLOOKUP silently returns the first one. That is the default here too, and the tool tells you how many keys are repeated and shows examples, so you know it happened. Choose “Take all matches” to get one result row per match instead, which is what you need for one-to-many data such as a customer and all their invoices. The summary sheet records which option you used.
Why keys do not match, and how to fix it
- Spaces: “P-001 ” with a trailing space is not “P-001”. Spaces are ignored by default.
- Numbers stored as text: one file has 1, the other “001” or “1.0”. Tick “Treat 001 and 1 as the same number”.
- Upper case and accents: “GÓMEZ” and “Gomez” match when both case and accents are ignored.
- Spelling differences such as “Acme Ltd” and “ACME Limited” need approximate matching: try Fuzzy match.
Known limitations
- Both files are loaded in memory: hundreds of thousands of rows work on a recent computer; on phones keep each file under about 20 MB.
- Matching is exact after the normalization options; it does not find similar spellings.
- Rows with an empty key never match and are listed in the “not found” sheets.
- Values, dates and number formats are kept; fonts, colours and formulas are not (formulas become their values).
- The header must be in the first non-empty row. Password-protected files cannot be opened.