How to Perform the Left Join in Excel - 3 Methods

There are 2 datasets below. The first dataset contains Product ID and Name. The second one contains Product Name, salesperson’s name, and value of Sales.

Left Join in Excel

Method 1 – Using the Power Query Editor to Perform Left Join in Excel

Step 1: Create Tables in Excel

Select B4:C9.
Go to the Insert tab >> click Table.

Use Power Query Editor to Perform Left Join in Excel

In Create Table, the cell range is selected.
Check My table has header option.
Click OK.

Creating Table to Perform Left Join in Excel

Go Table Design >> name the table in Table Name. Here, Product_List.

Create a table for E4:G9.
In Table Design >>Name it Sales in Table Name.

Step 2: Create Connections in Power Query Editor

Select the Product_List table.
Go to the Data tab >> click From Table/Range.

Create Connections to Perform Left Join in Excel

The table will open in the Power Query Editor.
Click Close & Load >> select Close & Load To.

Opening Power Query Editor to Perform Left Join in Excel

In Import Data, select Only Create Connection.
Click OK.
Create a connection for the Sales Table.

Opening Import Data Box to Perform Left Join in Excel

Step 3: Merge Tables

Go to the Data tab >> click Get Data >> click Combine Queries >> select Merge.

Merging Tables to Perform Left Join in Excel

In Merge, select the Product_List table and its Product column.
Select the Sales table and its Product column.
Select Left Outer as Join Kind.
Click OK.

Opening Merge Box to Perform Left Join in Excel

Merge1 table will open in the Power Query Editor.

Click the sign shown in the image below.
Check Sales Person and Sales.
Uncheck Use original column name as prefix.
Click OK.

The two columns are merged.

Click Close & Load >> select Close & Load To.

In Import Data, select Table.
Select Existing worksheet and enter B11 in the box.
Click OK.

This is the output.

Method 2 – Left Join Applying the Excel VLOOKUP Function

Steps:

Select B4:C9 and press Ctrl + C.

Left Join Applying Excel VLOOKUP Function

Select B11 and press Ctrl + V.

Add the Sales Person and Sales columns to the copied dataset.

Select D12 and enter the following formula.

=VLOOKUP(C12,$E$5:$G$9,{2,3},FALSE)

Press Enter, and you will see the value of both Sales Person and Sales.
Drag down the Fill Handle tool to autofill the formula.

In the VLOOKUP function, C12 is the lookup_value, E5:G9 is the table_array, 2 & 3 is col_index_num, and FALSE is range_lookup.

This is the output.

Left Join in Excel

Method 3 – Utilize the INDEX & MATCH Functions to Left Join in Excel

Utilize INDEX & MATCH Functions to Left Join Data in Excel

Steps:

Enter the following formula in D5.

=INDEX($F$5:$H$9,MATCH($C5,$F$5:$F$9,0),3)

Press Enter and drag down the Fill Handle to autofill this formula.

Formula Breakdown

MATCH($C5,$F$5:$F$9,0): The MATCH function returns the location of a lookup value. The Output 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. The formula becomes INDEX($F$5:$H$9,1,3). Here, the Output is {560}.