Join two Excel files
Upload your main file and the lookup file, choose the column they share, and download the first with the second file's columns already merged in.
What it is for
This is VLOOKUP as you know it: you have a table of orders with a customer code
and another with each customer's details, and you want the name and city sitting
next to every order. In Excel that means writing a VLOOKUP, dragging it down,
praying the references don't shift and converting it to values before it breaks
on a sort.
Here you upload both files, say which column joins them and download the result.
No formulas to maintain, no #N/A scattered around and nothing that falls apart
when you reorder rows.
The key column
The join runs on a key column: the value that appears in both files and connects them — the customer code, the SKU, the email. If you name none, the first column of each file is used.
You can type the column name, its Excel letter (A, B, AA) or its number.
The key is looked up in the lookup file for every row of the main file, just as
VLOOKUP would, but without sorting anything or worrying about approximate matches:
the join here is always exact.
Left join or inner join: what to do with unmatched rows
The difference is what happens to a row in the main file whose key is not found in the lookup file:
- Left join (the default) keeps all rows of the main file. Those with no
match come back with the brought columns blank. It is like a VLOOKUP that
leaves the cell empty instead of
#N/A. - Inner join keeps only the rows that found a match. Use it when you want to keep just what exists in both files.
When it finishes you are told how many rows matched and how many did not, so you can see the match rate at a glance.
Which columns are brought over
By default it brings every column of the second file except the key: repeating the key, which is already in the main file, only adds noise. If you prefer to bring only some, name them and they are added in that order.
If a column you bring has the same name as one already in the main file, a (B)
suffix is added to tell them apart: you will never end up with two Price
columns that no tool could separate.
If the same key is repeated in the lookup file, the join would be ambiguous: the first occurrence is used and you are warned how many keys were duplicated. If that happens, run the lookup file through remove duplicates first so each key is unique.
Frequently asked questions
How is this different from comparing two files?
Joining merges data: it takes columns from the second file and pastes them into the first. Comparing merges nothing; it only reports which rows were added, changed or removed between the two versions.
What happens to main-file rows with no match?
With a left join they are kept, with the brought columns blank. With an inner join they are dropped. That is exactly the decision you make with the join-type option.
What if the key doesn't match because of a space or capitals?
By default leading and trailing spaces are ignored and case is not distinguished,
so ABC123 and abc123 join without trouble. If your key really is
case-sensitive, turn that on.
Does the result come back in the format I uploaded?
Yes. If the main file is .xlsx or CSV, the result matches; a legacy .xls is
delivered as .xlsx. The lookup file can be in any of those formats.
Can I join on a key that is split across two columns?
The key is a single column. Join the parts first with split and merge columns in both files and join on the combined column.