How to Perform Left Join in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


Perform Left Join in Excel: 3 Easy Ways

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 salespersons, and value of Sales.

Now, using these datasets, we will show you how to left join data in Excel.

Left Join 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.

Use Power Query Editor to Perform Left Join in Excel

  • 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.

Creating Table to Perform Left Join in Excel

  • 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.
  • Afterward, go to the Table Design tab >> type Sales in the Table Name box.


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.

Create Connections to Perform Left Join in Excel

  • Now, the table will open in Power Query Editor.
  • After that, click on Close & Load >> select Close & Load To.

Opening Power Query Editor to Perform Left Join in Excel

  • 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.

Opening Import Data Box to Perform Left Join in Excel


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.

Merging Tables to Perform Left Join in Excel

  • 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.

Opening Merge Box to Perform Left Join in Excel

  • 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 Combine Two Tables Using Power Query in Excel


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.

Left Join Applying Excel VLOOKUP Function

  • 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.

Here, in the VLOOKUP function, we inserted cell C12 as lookup_value, cell range E5:G9 as table_array, 2 & 3 as col_index_num, and FALSE as range_lookup.
  • Thus, you can perform a left join by applying the VLOOKUP function.

Left Join in Excel

Read More: How to Perform Left Outer Join in Excel


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.

Utilize INDEX & MATCH Functions to Left Join Data in Excel

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 to the first table.


Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Practice Section


Download Practice Workbook

You can download the workbook to practice yourself.


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.


Related Articles


<< Go Back to Power Query Joins | Power Query Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo