Full Join Two Tables in Google Sheets (Array Formula)

A full join in Google Sheets combines all rows from both tables, regardless of whether they have matching values in the common key column. Matching records are merged into the same row, while unmatched records from either table are included with blank values for the missing columns.

It’s useful when you want to merge two related tables without losing records that exist in only one of them.

Table of Contents

Prerequisites

Before proceeding, ensure the following:

Both tables share a common key column.
The common key columns must not contain blank cells within the data range.
If you include the header row in the ranges, the join key must have the same column header in both tables. Otherwise, the header row will not be included in the joined result.

Google Sheets Full Join Formula

Use the following array formula to perform a full join between two tables. The formula returns the complete joined table automatically, so there’s no need to copy it down.

=ArrayFormula(
  LET(
    lt, left_table_range, 
    lt_id, left_table_key_range, 
    rt, right_table_range, 
    rt_id, right_table_key_range, 
    key, TOCOL(VSTACK(lt_id, IFNA(XMATCH(rt_id, lt_id)/0, rt_id)), 3),
    look_up, IFNA(VLOOKUP(key, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt)+1), 0), HSTACK(key, )), 
    s, IFNA(HSTACK(lt, look_up)),
    FILTER(s, CHOOSECOLS(s, COLUMNS(lt)+1)<>"")
  )
)

Important: This formula assumes the join key contains unique values in the right table. If the right table contains duplicate keys, VLOOKUP returns only the first matching row for each key. For formulas that support duplicate keys, see Google Sheets Joins with Duplicate IDs (Master Guide).

Sample Tables

We’ll use two simple tables to demonstrate how a full join works. The table on the left contains Employees data, while the table on the right contains Departments data. The tables are joined on the Department ID column. Notice that the Department ID values are unique in the right table.

Sample Employees and Departments tables used to demonstrate a full join in Google Sheets

If you’d like to follow along or inspect the formula, make a copy of the sample Google Sheets file using the button below.

Copy Sample Sheet

How to Apply the Full Join Formula

To perform a full join on the sample tables, enter the following array formula in cell I1:

=ArrayFormula(
  LET(
    lt, A1:C, 
    lt_id, C1:C, 
    rt, E1:G, 
    rt_id, E1:E, 
    key, TOCOL(VSTACK(lt_id, IFNA(XMATCH(rt_id, lt_id)/0, rt_id)), 3),
    look_up, IFNA(VLOOKUP(key, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt)+1), 0), HSTACK(key, )), 
    s, IFNA(HSTACK(lt, look_up)),
    FILTER(s, CHOOSECOLS(s, COLUMNS(lt)+1)<>"")
  )
)

The formula returns the full join automatically. The result includes three consecutive Department ID columns:

The first column contains the keys from the left table.
The second column contains all unique keys from both tables.
The third column contains the keys from the right table.

Full join result in Google Sheets with the duplicate Department ID columns highlighted for removal

In most cases, you’ll only need the second column because it represents the complete set of join keys. You can either hide the first and third columns or remove them using CHOOSECOLS or QUERY.

Remove the Duplicate Join Key Columns

Replace the following expression:

FILTER(s, CHOOSECOLS(s, COLUMNS(lt)+1)<>"")

with one of the following alternatives.

Using CHOOSECOLS

CHOOSECOLS(
  FILTER(s, CHOOSECOLS(s, COLUMNS(lt)+1)<>""),
  1, 2, 4, 6, 7
)

Using QUERY

QUERY(
  FILTER(s, CHOOSECOLS(s, COLUMNS(lt)+1)<>""),
  "SELECT Col1, Col2, Col4, Col6, Col7",
  1
)

Both formulas remove the duplicate Department ID columns while preserving the full join result.

Adapting the Full Join Formula

To use the formula with your own tables, update the following variables to match your data:

lt – The range of the left table.
lt_id – The join key column in the left table.
rt – The range of the right table.
rt_id – The join key column in the right table.

You don’t need to modify the key variable. It automatically builds a combined key list using all keys from the left table and any unmatched keys from the right table.

You can include or exclude the header row in the ranges. If you include it, make sure the join key has the same column header in both tables; otherwise, the header row won’t appear in the joined result.

Note: The joined table may lose number formatting, such as dates, currencies, and percentages. If necessary, reapply the appropriate number format from Format → Number after the formula returns the result.

How the Formula Works

The full join formula is built around three key expressions: key, look_up, and the final HSTACK. Together, these expressions build the combined key list, retrieve matching rows from the right table, and assemble the final full join.

Create the Combined Join Key

The following expression builds the combined join key list:

key, TOCOL(VSTACK(lt_id, IFNA(XMATCH(rt_id, lt_id)/0, rt_id)), 3)

Starting with all keys from the left table.
Finding keys that exist only in the right table.
Appending those unmatched right-table keys to the bottom of the list.

This generates a combined key list containing all keys from the left table, followed by the keys from the right table that don’t exist in the left table.

Combined join key list generated for a Google Sheets full join, showing all left table keys followed by unmatched keys from the right table

Retrieve Matching Rows

look_up, IFNA(VLOOKUP(key, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt)+1), 0), HSTACK(key, ))

Searches each key in the combined key list within the right table and returns the matching row, with the right table’s join key as the first column.

If a key isn’t found in the right table, VLOOKUP returns #N/A for every column in that row. The IFNA(..., HSTACK(key, )) expression replaces only the first #N/A (the prepended join key column) with the corresponding key from the combined key list, while the remaining columns in that row continue to contain #N/A.

Combine Both Tables

s, IFNA(HSTACK(lt, look_up))

Horizontally combines the left table with the lookup results, producing an intermediate full join result. Rows that exist in only one table are still included, with blank values in the columns from the other table.

FILTER(s, CHOOSECOLS(s, COLUMNS(lt)+1)<>"")

The intermediate array s may contain blank rows at the bottom because the left table range is typically open-ended. This expression removes them by filtering on the appended join key column, which is the first column after the left table. Rows with a blank join key are discarded, leaving only valid full join results.

Frequently Asked Questions

What is a full join in Google Sheets?

A full join combines two tables based on a common key column and returns all rows from both tables. Matching records are merged into the same row, while unmatched records from either table are included with blank values for the missing columns.

Can I perform a full join on more than two tables?

Yes, but not in a single step with the formula in this tutorial. To join a third table, first perform a full join on the first two tables, then use the resulting table as the left table and full join it with the third table.

For best results, the join key should contain unique values in the additional table.

Can I use the QUERY function for a full join?

No. The QUERY function cannot perform a true full join in Google Sheets.

Conclusion

You can now perform a full join in Google Sheets using a single array formula. Once you’ve updated the table ranges and join key columns, the formula automatically returns matching rows together with unmatched rows from both tables.

If you’d like to learn other types of table joins, check out these related tutorials: