Left Join Two Tables in Google Sheets (Array Formula)

A left join in Google Sheets combines two tables using a common key (such as an ID), keeping all rows from the left table and returning matching values from the right table. If no match exists, the corresponding columns from the right table remain blank.

In this tutorial, you’ll learn how to perform a left join using a single array formula. To follow along, your tables should share a common key, such as an employee ID, department ID, customer ID, or product code.

Table of Contents

Google Sheets Left Join Formula

=ArrayFormula(
  LET(
    lt, left_table_range,
    lt_id, left_table_key_range,
    rt, right_table_range,
    rt_id, right_table_key_range,
    look_up, IFNA(VLOOKUP(lt_id, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0)),
    FILTER(HSTACK(lt, look_up), lt_id<>"")
  )
)

Do Your Tables Contain Duplicate IDs?

This formula assumes that the lookup key is unique in the right table. If multiple rows share the same key, VLOOKUP returns only the first match. For solutions that handle duplicate keys, see Google Sheets Joins with Duplicate IDs (Master Guide).

Sample Data

Click Copy Sample Sheet below to create your own copy and follow along.

Copy Sample Sheet

The following sample tables contain employee data and department details. Both tables share the Department ID, which we’ll use as the matching key.

Sample employee and department tables used for a left join in Google Sheets

Our goal is to match the Department ID in both tables and append the Department and Manager columns to each employee record.

Left Join Formula

For the sample data above, use the following formula.

=ArrayFormula(
  LET(
    lt, A1:C,
    lt_id, C1:C,
    rt, E1:G,
    rt_id, E1:E,
    look_up, IFNA(VLOOKUP(lt_id, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0)),
    FILTER(HSTACK(lt, look_up), lt_id<>"")
  )
)

Result of a left join in Google Sheets showing employee records with matching department details

The result includes the Department ID from both tables, so the column appears twice. If you only need one copy, hide the duplicate column or replace the final expression, FILTER(HSTACK(lt, look_up), lt_id<>""), with a CHOOSECOLS formula.

For example:

CHOOSECOLS(FILTER(HSTACK(lt, look_up), lt_id<>""), 1, 2, 3, 5, 6)

Note: Formatting from the right table (such as dates, currencies, or percentages) isn’t preserved. If necessary, reapply the desired number format to the corresponding output columns.

How to Use the Formula with Your Own Data

To adapt the formula to your own dataset, replace the ranges assigned in the LET function with the corresponding ranges in your sheet.

Update the following assignments to match your sheet:

lt: The data range of the left table, including the header row.
lt_id: The key column range (for example, Department ID) in the left table.
rt: The data range of the right table, including the header row.
rt_id: The key column range in the right table.

These four assignments are the only parts of the formula you’ll typically need to modify. The rest of the formula works without any changes, regardless of the number of columns in the right table.

How the Formula Works

The formula works in three steps:

Searches the Department ID from the left table in the right table.
Returns the matching department details for each employee.
Combines those values with the original employee records while preserving every row from the left table.

Frequently Asked Questions

Can I use the `QUERY` function for a left join?

Not directly. The native QUERY function in Google Sheets can filter, sort, group, and summarize data, but it doesn’t support SQL-style JOIN operations across separate ranges.

Although you can simulate a left join by combining QUERY with other functions, the LET + VLOOKUP + HSTACK approach shown here is simpler, easier to maintain, and works well for most datasets.

Can I reorder the columns in the left-joined result?

Yes. You can wrap the final output with either CHOOSECOLS or QUERY.

For example, replace:

FILTER(HSTACK(lt, look_up), lt_id<>"")

with:

QUERY(
     FILTER(HSTACK(lt, look_up), lt_id<>""),
     "SELECT Col1, Col2, Col3, Col5, Col6",
     1
)

This example returns only the selected columns and excludes blank rows from the result.

Conclusion

A left join is one of the most practical ways to combine related tables in Google Sheets. Once you understand this pattern, you can reuse the same formula for employee records, sales reports, inventory lists, customer databases, and many other datasets by simply updating the table ranges and key columns.