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.


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.

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

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

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

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

Practice Section


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!


Related Articles

Arin Islam

Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo