How to Left Join Two Tables in Google Sheets

Published on

To left join two tables in Google Sheets, you can use an array formula. Though the formula may seem complex, customizing it for your specific table ranges is a simple task.

The LET function is utilized to assign names to expressions. You will need to specify the table ranges and ID columns in both tables, and the formula will take care of the rest.

In a left join, two tables are combined into a new one, retaining all rows from the first table (left table) and incorporating matching rows from the second table (right table) based on a shared field (e.g., ID).

A crucial prerequisite for left joining two tables is a shared field in both tables, such as IDs (e.g., employee IDs, product IDs).

Note: If you have duplicated IDs in both ID columns (left and right tables), please check out this guide: Conquer Duplicate IDs: Master Left, Right, Inner, & Full Joins in Google Sheets.

Are you prepared to unleash the potential of left joins? Explore the next section for a comprehensive breakdown of the formula and its application!

Left Join Two Tables: Sample Tables and Resulting Table

This type of left join between two tables typically requires only a VLOOKUP formula in Google Sheets. However, we’ll incorporate additional functions to facilitate adaptation to tables of varying sizes.

Table 1: Orders

Order_IDCustomer_NameProduct_IDQuantity_Cum
1Amy100140
2Gary100140
3Evelyn1003500
4Stephen1003120
5Clarence100240
6Melissa10051000

This table contains 6 records (6 orders) and four columns, and the Product_ID in the third column uniquely identifies each product.

Table 2: Products

Product IDProduct NamePrice_Cum
1001Sand8
1002Gravel 10-20 mm10
1003Gravel 5-10 mm10
1004Roadbase10

This table contains 4 records (4 products) and three columns, and the Product_ID in the first column uniquely identifies each product. Please note that in this table, the unique identifiers, i.e., Product IDs in the first column, must not be repeated.

Expected Result:

Order IDCustomer NameProduct IDQuantity_CumProduct NamePrice_Cum
1Amy100140Sand8
2Gary100140Sand8
3Evelyn1003500Gravel 5-10 mm10
4Stephen1003120Gravel 5-10 mm10
5Clarence100240Gravel 10-20 mm10
6Melissa10051000N/AN/A

Please scroll down to see the screenshot of the above tables in Google Sheets.

Array Formula for Left Joining Two Tables in Google Sheets

This formula performs a left join between two tables in Google Sheets, retaining all records from the left table. It assumes unique records exist in the right table’s ID column.

Formula:

=ArrayFormula(
     LET(
          lt, A2:D8, lt_id, C2:C8, rt, F2:H6, rt_id, F2:F6, 
          look_up, IFNA(VLOOKUP(lt_id, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0)), 
          HSTACK(lt, look_up)
     )
)
Array Formula for Left Joining Two Tables in Google Sheets

You can replace the formula expression, i.e., HSTACK(lt, look_up) in the formula with CHOOSECOLS(HSTACK(lt, look_up), {1, 2, 3, 4, 6, 7}) to select the required columns and also in the order you want.

Here is the syntax of the CHOOSECOLS function:

CHOOSECOLS(array, [col_num1, …])

Note: The formula may convert dates to date values. Therefore, if any column in the left or right table contains a date field, the corresponding field in the resulting table must be formatted as a date using Format > Number > Date.

You can find the left join array formula in cell A12 of the second sheet in the provided sample sheet below.

Sample Sheet

Formula Breakdown

The formula utilizes the LET function to assign ‘name’ to ‘value_expression’ results and returns the result of the ‘formula_expression’, which represents the resulting table from the left join.

Syntax of the LET Function:

LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

LET Assignments:

  • lt (name1): Defines the data range of the left table, including the header row.
    • A2:D8 (value_expression1): Data range of the left table.
  • lt_id (name2): Defines the ID column of the left table, including the header row.
    • C2:C8 (value_expression2): ID column of the left table.
  • rt (name3): Defines the data range of the right table, including the header row.
    • F2:H6 (value_expression3): Data range of the right table.
  • rt_id (name4): Defines the ID column of the right table, including the header row.
    • F2:F6 (value_expression4): ID column of the right table.

Note: You only need to specify the above value_expressions 1 to 4 concerning your tables. The left join array formula will take care of the rest.

Remaining LET Assignments:

  • look_up (name5):
    • IFNA(VLOOKUP(lt_id, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0)) (value_expression5): VLOOKUP searches for the IDs from the left table (lt_id) within the ID column of the right table (rt_id) and returns matching records from the right table (rt).
      • HSTACK: Joins the ID column of the right table (rt_id) with the entire right table data (rt).
      • SEQUENCE: Generates a range of numbers representing the column positions in the right table (2 to the number of columns as the first column is the stacked rt_id).
      • The final argument (0) specifies an exact match.
      • The VLOOKUP function returns matching records from the right table for each ID in the left table.

Formula_Expression:

  • HSTACK(lt, look_up): This segment merges the data from the left table (lt) with the lookup result (look_up). However, it’s important to note that the lookup result comprises only the matching records from the right table, not the entire right table. This distinction is crucial as it precisely accomplishes the intended left join functionality.

Conclusion

You now possess a potent tool for left joins in Google Sheets. The merged table can be fine-tuned with QUERY, enabling actions such as column reordering, data sorting, and aggregations.

For further exploration of the QUERY function’s capabilities, you can find numerous tutorials within this blog. These resources will guide you through the syntax and provide practical examples to help you master this versatile function.

Resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.