Excel Join Tables

 admin
  1. Excel Join Tables Online
  2. Can You Join Tables In Excel
  3. Excel Join Tables
  4. Join Tables In Access
  5. Excel Join Two Tables Based On Column
  6. Excel Join Tables Worksheet

Click Run to execute the join tables process and write data results into a new worksheet. Cancel Click Cancel to close the Join Tables window. INNER JOIN Selects all rows from both tables as long as there is a match between the columns in both tables. LEFT OUTER JOIN Selects all rows from the left table, with the matching rows in the right table. Summary To join or merge tables that have a common id, you can use the INDEX and MATCH functions. In the example shown, the formula in E5 is: = INDEX(data,MATCH($C5, ids,0),2).

A good database is always structured. It means different entities have different tables. Although, Excel is not a database tool but is often used to maintain small chunks of data.

Many times we get the need of merging tables in order too see a relationship and churn out some useful information.

Feb 25, 2021 Join multiple tables into one with Excel Power Query In situations when you need to combine two or more tables with different numbers of rows and columns, Excel Power Query may come in handy. However, please be aware that joining tables with Power Query cannot be done with a mere couple of clicks. Combine 2 tables. The last example shows how you can combine rows in 2 tables (in 2 different workbooks) based on criterion and insert the result in a new workbook. In this case a table with contact persons and one with company information are combined, if company names match.

In databases like SQL, Oracle, Microsoft Access, etc, it is easy to join tables using simple queries. But in excel we don't have JOINs but we can still join tables in excel. We use Excel Functions to merge and join data tables. Perhaps it is a more customizable merge than SQL. Let’s see the techniques of merging excel tables.

Merge Data in Excel Using VLOOKUP Function

To merge data in excel, we should have at least one common factor/id in both tables, so that we can use it as a relation and merge those tables.
Consider these two tables below. How can we combine these data tables in Excel?


The Order Table has order details and the Customer table contains customer details. We need to prepare a table that tells which order belongs to which customer name, customer’s points, house number, and his joining date.

The common id in both tables is Cust.ID which can be used for merging tables in excel.

There are three methods of merging tables using VLOOKUP Function.

Retrieve Each Column With Column Index

In this method, we will use simple VLOOKUP to add these tables in one table. So to retrieve name write this formula.

[Customers is Sheet1!$I$3:$M$15.]

To merge points in table, write this formula.

=VLOOKUP(B3,Customers,3,0)

To merge house no in table, write this formula.


Here we merged two tables in excel, each column one by one in the table. This is useful when you have only few columns to merge. But when you have multiple columns to merge, this is can be a hectic task. So to merge multiple tables we have different approaches.

Excel Join Tables Online

Merge Tables Using VLOOKUP and COLUMN Function.

When you want to retrieve multiple adjacent columns, use this formula.

=VLOOKUP(lookup_value,table_array,COLUMN()-n,0)

Here the COLUMN function just returns the column numbers in which the formula is being written.

Excel Join Tables

n is any number which adjusts the column number in table array,

In our example, the formula for table merging will be:

Once you write this formula, you will not have to write the formula again for other columns. Just copy it in all other cells and columns.
How It Works

Ok! In the first column, we need a name, which is the second column in customer table.

Here the main factor is COLUMN()-2. The COLUMN function returns the column number of current cell. We are writing the formula in D3, hence we will get 4 from COLUMN(). Then we are subtracting 2 which makes 2. Hence finally our formula simplifies to =VLOOKUP($B3,Customers,2,0).

When we copy this formula in E columns we will get the formula as =VLOOKUP($B3,Customers,3,0). Which retrieves 3rd column from customer table.

Merge Tables Using VLOOKUP-MATCH Function
This one is my favorite way of merging tables in excel using the VLOOKUP function. In the above example, we retrieved column in serial. But what if we had to retrieve random columns. In that case above Techniques will be useless. The VLOOKUP-MATCH technique uses column headings to merge cells. This is also called VLOOKUP with Dynamic Col Index.

For our example here, the formula will be this.

=VLOOKUP($B3,Customers,MATCH(D$2,$J$2:$N$2,0),0)


Here, we are simply using MATCH function to get the appropriate column number. This called Dynamic VLOOKUP.

Using INDEX-MATCH to Table Merging in Excel
The INDEX-MATCH is very powerful lookup tool and many time it is referred as better VLOOKUP function. You can use this to for combining two or more tables. This will also allow you to merge columns from left of the table.

This was a quick tutorial about merging and joining tables in excel. We explored several ways of merging two or more tables in excels. Feel free to ask question about this article or any other query regarding excel 2019, 2016, 2013 or 2010 in the comments section below.

Related Articles:

Can You Join Tables In Excel

Popular Articles :

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

The term “Join” comes from the database world, and I’ll admit that I’ve struggled with understanding it... especially when you combine it with some other keywords. So this week I thought it would be good to explore it in a bit more detail, specially to show the different results when we merge tables using outer joins.

There are actually three flavours of Outer Join you could come across, and each work in different ways. Those three flavours are:

Excel Join Tables

  • Left Outer join
  • Right Outer join
  • Full Outer join

Join Tables In Access

Which… to an Excel person… mean very little. To confuse matters more, there are also Inner joins, and Anti joins. (We’ll look at those next week though.)

To illustrate the different join types, we are going to work with the set of data shown below (which you can download here.)

So two tables of data, one called Transactions, and one called ChartOfAccounts.

Now, the key piece you want to watch here is that we need the Account-Dept combination to exist in both tables in order to make a perfect join between them. Think VLOOKUP for a second… if you tried to make a VLOOKUP for account 10045 from the Transactions table against the ChartOfAccounts table, what would you get? Of course, you’d get #N/A since 10045 doesn’t exist in the ChartOfAccounts table.

In this case we have items in both tables that don’t exist in the other. (The yellow records in the Transactions table don’t have a match in the ChartOfAccounts table, and the red records in ChartOfAccounts don’t have a match in the Transactions table.) With these differences we can test how each of the first three join types available to us behave when we try to merge the data in both tables together.

Excel Join Tables

Of course, the first thing we need is a pointer to each table for Power Query to work. So let’s set that up first.

  • Click in the Transactions table –> New Query –> From Table
  • Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection
  • Click in the COA Table –> New Query –> From Table
  • Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

I also right clicked each query in the Workbook Queries pane and choose to move them to a group I called Source, just to keep them organized:

This will work nicely. We have a pointer to both tables, but didn’t cause any data duplication by loading a new copy to a worksheet.

Okay, let’s get started. We need to join these based on the combination of the Account and Dept number. Fortunately we can use the trick discussed in this post to do this with creating a concatenated key manually first:

  • Right click the Transactions query and choose Merge
  • Select ChartOfAccounts for the bottom table
  • For the top query (Transactions) select Account, hold down CTRL and select Dept
  • For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept

Your join selections should now look like this:

Notice that the Join Kind in the bottom is “Left Outer (all from first, matching from second). Let’s click OK and see what that means.

When you get into the Power Query editor:

  • Right click the NewColumn column –> Rename –> COA
  • Click the Expand icon on the top right of the COA column
  • Leave all the defaults and click OK

The resulting query should look as follows (barring the colour of course):

So this is a “Left Outer Join” – the default choice for Power Query. It returns all entries in the left table (or top in the case of the power query editor) and returns the matching values it finds based on the lookup column. Essentially this is the same as VLOOKUP. It returns a match, except where it can’t find a matching record. (The main difference between VLOOKUP and Power Query is that if Power Query found multiple matching records, it would return all of them, not just one.)

Again, the key point here is that every value from the Left table is returned, whether there is a match or not in the Right table. The yellow rows here match the yellow rows in the original Transaction table shown at the beginning of the post.

But… notice also that accounts 10040 and 11000 (the red accounts in the COA table) do not shown in the listing at all. This is your Left Outer join in action. It pulls all records from the left table, any matches from the right (or null if no records on the right match). It never looks at the right side at all to see if records exist there that don’t exist in the left hand table.

Okay, so now we can see what’s happening here, let’s finish it off:

  • Change the name of the query to LeftOuter
  • Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

Rather than discuss the difference here, let’s just demo it. One caveat… it’s important to get a clear understanding that you start with same base table in order to make parallel comparisons!

  • Right click the Transactions query and choose Merge
  • Select ChartOfAccounts for the bottom table
  • For the top query (Transactions) select Account, hold down CTRL and select Dept
  • For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
  • Change the Join type to “Right Outer (all from second, matching from first)”
  • Click OK

And then we’ll do the same thing we did before so that we can compare the results:

  • Right click the NewColumn column –> Rename –> COA
  • Click the Expand icon on the top right of the COA column
  • Leave all the defaults and click OK

If you followed along correctly, your result should look like this:

Excel Join Tables

Notice the big difference here? This time the values from the Right table (ChartOfAccounts) show whether there is a match in the Left (Transactions) table or not. The red rows (containing 10040 and 11000 from our original table) are now present, where they weren’t in the previous scenario.

But the (yellow) items that were in the Left (Transaction) table which don’t have a match in the Right (ChartOfAccounts) table? They’re nowhere to be seen!

Where I now know I struggled with this when writing SQL code from scratch is that no one ever explained to me which table was Left and which was Right. Lacking that knowledge there really isn’t anything to explain what these joins are truly doing. But now that we can see that the table we start our merge from is the Left table, and the one we are joining to it is the Right table… it starts to make a LOT more sense. The only kicker we have with Power Query is that the Left table is the top in our merge dialog, and the Right is the bottom. But you can certainly see why the UI was designed this way (imagine trying to fit it on your screen if it was side by side?)

At any rate, we can now compare and contrast those two joins. Let’s finish this one off and look at the final join we’ll examine today.

Excel Join Two Tables Based On Column

  • Change the name of the query to RightOuter
  • Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

By now, you know the drill…

Excel Join Tables Worksheet

  • Right click the Transactions query and choose Merge
  • Select ChartOfAccounts for the bottom table
  • For the top query (Transactions) select Account, hold down CTRL and select Dept
  • For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
  • Change the Join type to “Full Outer (all from second, matching from first)”
  • Click OK

And now modify the query:

  • Right click the NewColumn column –> Rename –> COA
  • Click the Expand icon on the top right of the COA column
  • Leave all the defaults and click OK
Excel

And what do we have?

Aha! So the Full Outer join makes sure we’ve got all items from both sides. I can see this being SUPER useful for trying to compare to lists of transactions like in a bank reconciliation. Transactions that match would get lined up nicely, and any that needed attention would have a bunch of nulls beside them on either side. Very cool. (I’ll have to do a post on that some day!)

This shows how to merge tables using Outer Joins… If you'd like to learn about the three remaining join types shown below, you can do so at this article:

  • Inner Join
  • Left Anti Join
  • Right Anti Join