Looking for ways to perform the left join in Excel? Then, this is the right place for you. Sometimes we want to join columns from another table from the left. To do that, you can follow different ways. Here, you will find 3 ways to perform the left join in Excel.
Download Practice Workbook
You can download the workbook to practice yourself.
3 Easy Ways to Perform Left Join in Excel
Here, we have 2 datasets containing information about some products. The first dataset contains the Product ID and Name. On the other hand, the second dataset contains the Product Name, names of Sales Persons, and value of Sales.
Now, using these datasets we will show you how to left join data in Excel.
1. Use Power Query Editor to Perform Left Join in Excel
In the first method, we will show you how to use Power Query Editor to perform left join in Excel. To do that, you have to create tables using the datasets and then merge the tables using Power Query Editor.
Follow the steps given below to do it on your own.
Step-01: Create Tables in Excel
In the first step, you have to create tables using the datasets and give names to those tables.
- To start with, select cell range B4:C9.
- Then, go to the Insert tab >> click on Table.
- Now, the Create Table box will open.
- After that, you can see that the cell range has already been selected.
- Next, turn on My table has header option.
- Lastly, click on OK.
- Then, go to the Table Design tab >> type any name you want to give the table in the Table Name box.
- Here, we will type Product_List.
- Similarly, create a table using cell range E4:G9.
- After that, go to the Table Design tab >> type Sales in the Table Name box.
Read More: How to Use Join Formula in Excel (6 Practical Examples)
Step-02: Create Connections in Power Query Editor
Next, we will create connections using the tables in Power Query Editor. To do that, go through the steps given below.
- Firstly, select the Product_List table.
- Then, go to the Data tab >> click on From Table/Range.
- Now, the table will open in Power Query Editor.
- After that, click on Close & Load >> select Close & Load To.
- Next, the Import Data box will appear.
- Afterward, select the Only Create Connection option.
- Further, click on OK.
- Thus, a connection will be created.
- Similarly, create a connection for the Sales Table.
Step-03:Â Merge Tables
In the last step, we will merge the tables using the Get Data feature.
- In the beginning, go to the Data tab >> click on Get Data >> click on Combine Queries >> select Merge.
- Now, the Merge box will open.
- Firstly, select the Product_List table and its Product column.
- Then, select the Sales table and its Product column.
- After that, select Left Outer as Join Kind.
- Lastly, click on OK.
- Now, the Merge1 table will open in the Power Query Editor.
- Afterward, click on the sign given below in the image.
- Then, turn on the options for Sales Person and Sales columns.
- Further, turn off the Use original column name as prefix option.
- Finally, click on OK.
- Now, you will see that the two columns have been merged.
- Next, click on Close & Load >> select Close & Load To.
- After that, the Import Data box will open.
- Then, select the Table option.
- Now, select Existing worksheet and insert a Cell B11 in the box.
- Lastly, click on OK.
- Thus, you can perform a left join using Power Query Editor.
Read More: How to Join Tables in Excel (6 Suitable Methods)
2. Left Join Applying Excel VLOOKUP Function
You can also use the VLOOKUP function to join data from the left in Excel. To do that, follow the steps given below.
Steps:
- Firstly, select cell range B4:C9 and press Ctrl + C.
- Then, select Cell B11 and press Ctrl + V.
- After that, add the Sales Person and Sales columns in the copied dataset to join data from the second dataset.
- Now, select Cell D12 and insert the following formula.
=VLOOKUP(C12,$E$5:$G$9,{2,3},FALSE)
- Next, press Enter and you will get the value of both Sales Person and Sales.
- Further, drag-down the Fill Handle tool to autofill this formula for the rest of the cells.
- Thus, you can perform a left join by applying the VLOOKUP function.
Read More: How to Perform Outer Join in Excel (2 Easy Ways)
3. Utilize INDEX & MATCH Functions to Do Left Join in Excel
In the last method, we will utilize the INDEX & MATCH functions to join data in Excel. Here, we will join the data of Sales values in the first table using these functions.
Steps:
- In the beginning, insert the following formula in Cell D5.
=INDEX($F$5:$H$9,MATCH($C5,$F$5:$F$9,0),3)
- Then, press Enter and drag-down the Fill Handle tool to autofill this formula for the rest of the cells.
🔎 How Does the Formula Work?
- MATCH($C5,$F$5:$F$9,0): The MATCH function returns the location of a lookup value. The Output of this formula is {1}.
- INDEX($F$5:$H$9,MATCH($C5,$F$5:$F$9,0),3): The INDEX function returns a cell value of a lookup value. So, the formula becomes INDEX($F$5:$H$9,1,3). In this case, the Output is {560}.
- Finally, the Sales column will be added with the first table.
Read More: How to Inner Join in Excel (2 Easy Methods)
Practice Section
In the article, you will find an Excel workbook like the image given below to practice on your own.
Conclusion
So, in this article, we have shown you 3 ways to perform left join in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. And, visit ExcelDemy for many more articles like this. Thank you!