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

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.

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

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.

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.

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.

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

Download Practice Workbook

You can download the workbook to practice yourself.

Perform Left Join.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!