Join Two Tables on a Key Column
Paste your main table and a second table, choose the column they share, and pick the columns to add. Keep every row, only matches, everything, or list the rows with no match.
Drag a file here or choose one
Drag a file here or choose one
Worked examples
Add customer names to orders
Orders: Order,Customer ID / 1001,C01 / 1003,C09 Customers: Customer ID,Name / C01,Ann Lee
Order,Customer ID,Name 1001,C01,Ann Lee 1003,C09,
Every row of the orders table is kept. C09 has no match, so its Name is blank.
Find missing customers
Same two tables, rows with no match only
Order,Customer ID 1003,C09
Only the order whose customer is not in the second table is listed.
About Join Two Tables on a Key Column
Joining two tables means lining up their rows on a value they share, such as a customer number, a product code or an email address, and combining the columns. It is the job a look-up formula does in a spreadsheet, here without writing any formulas. Paste your main table, paste the table that holds the extra information, choose the key column in each and tick the columns to bring across.
You decide which rows to keep. Keeping every row of the first table leaves blanks where nothing matched, which shows you the gaps. Keeping only matches gives a smaller table. Keeping everything from both tables is a full join, and listing only the rows of the first table with no match is the quickest way to find records that are missing from the other system. The counts show how many rows matched and how many did not.
How to use Join Two Tables on a Key Column
- Paste or open the first table, which keeps its rows, and then the second table, which holds the values to look up.
- Choose the key column in each table. If the column names match, the second is chosen for you.
- Tick the columns to bring across from the second table, and choose which rows to keep.
- Check the counts of matches and gaps, then download the joined table.
Common uses
- Adding customer names and cities to a list of orders
- Finding customers who appear in one system but not another
- Adding prices from a product list to a list of sales
- Matching a payments file against an invoice list
Frequently asked questions
What if a key appears more than once in the second table?
You choose. Use the first match, which keeps the first table's rows unchanged in number, or add a row for each match, which repeats the row from the first table once for each matching row.
Why are some rows not matching when they look the same?
Usually a space on the end of one value or a difference in capital letters. Spaces at the ends are ignored here and capitals are ignored if the box is ticked. Hidden characters can also be the cause; the CSV cleaner removes them.
What is the difference between the row choices?
Every row of the first table is a left join. Only rows with a match is an inner join. Every row of both tables is a full join. The last choice returns just the rows of the first table that found no match.
What happens if both tables have a column with the same name?
The column from the second table gets (B) added to its name so that the two can be told apart.
Are blank keys matched?
No. A row with a blank key never matches anything, so blank cells do not join thousands of unrelated rows together.