How to Perform a Left Join in Excel

Performing a left join in Excel involves combining data from two tables based on a common key column. The result includes all rows from the left table and matching rows from the right table. If no match is found in the right table, the result will contain NULL or blank values.

Follow these steps to perform a left join in Excel:

1. Open Excel and load the two tables you want to join. Ensure both tables have a common key column, such as an ID or name, that you will use to join the tables.
2. Add a new worksheet where you want to display the joined data.
3. In the new worksheet, copy the headers from the left table (the table you want to display all rows from) and paste them in the first row.
4. Copy the headers from the right table (the table you want to display matching data from) and paste them to the right of the left table headers, excluding the common key column header.
5. In the second row, under the first header of the left table, enter the formula =IFERROR(INDEX(LeftTableRange, ROW() - 1, COLUMN()), ""). Replace LeftTableRange with the actual range of the left table, including the headers.
6. Drag this formula across all the columns of the left table and down to the number of rows you expect in the final joined data.
7. In the first empty cell of the second row, to the right of the left table data, enter the following formula:

=IFERROR(
    INDEX(
        RightTableRange,
        MATCH($A2, RightTableKeyColumn, 0),
        COLUMN() - NumberOfLeftTableColumns
    ),
    ""
)

Replace RightTableRange with the actual range of the right table, including the headers. Replace RightTableKeyColumn with the range of the key column in the right table. Replace NumberOfLeftTableColumns with the number of columns in the left table, excluding the common key column.

8. Drag this formula across all the columns of the right table (excluding the key column) and down to the number of rows you expect in the final joined data.

After completing these steps, the joined data should be displayed in the new worksheet, with all rows from the left table and matching rows from the right table.

Example

Let's say you have two tables: Orders and Customers. You want to perform a left join using the "Customer ID" as the common key column.

1. Open Excel and load the Orders and Customers tables. Ensure both tables have a "Customer ID" column.
2. Add a new worksheet called "Joined Data."
3. Copy the headers from the Orders table and paste them in the first row of the "Joined Data" worksheet.
4. Copy the headers from the Customers table and paste them to the right of the Orders headers, excluding the "Customer ID" header.
5. In cell A2 of the "Joined Data" worksheet, enter the formula =IFERROR(INDEX(Orders!$A$1:$C$6, ROW() - 1, COLUMN()), ""). In this example, the Orders table range is A1:C6.
6. Drag this formula across all the columns of the Orders table and down to the number of rows you expect in the final joined data.
7. In cell D2 of the "Joined Data" worksheet, enter the following formula:

=IFERROR(
    INDEX(
        Customers!$A$1:$C$4,
        MATCH($A2, Customers!$A$1:$A$4, 0),
        COLUMN() - 3
    ),
    ""
)

In this example, the Customers table range is A1:C4, the key column is A1:A4, and the number of columns in the Orders table is 3.

8. Drag this formula across all the columns of the right table (excluding the key column) and down to the number of rows you expect in the final joined data.

Now, the "Joined Data" worksheet should display the left-joined data from the Orders and Customers tables.