A right join (also called a right outer join) combines two tables by keeping every record from the right table and adding matching data from the left table. Although Google Sheets doesn’t include a built-in RIGHT JOIN function, you can achieve the same result with a single dynamic array formula.
In this tutorial, you’ll learn how to perform a right join, adapt the formula to your own data, and understand how each part of the formula works.
Before You Begin
- Both tables must share a common key column.
- The formula automatically adapts to the number of columns in both tables.
Google Sheets Right Join Formula
=ArrayFormula(
LET(
lt, left_table_range,
lt_id, left_table_key_range,
rt, right_table_range,
rt_id, right_table_key_range,
merge1, REDUCE(TOCOL(,1), TOCOL(rt_id, 1), LAMBDA(a, v, IFNA(VSTACK(a, HSTACK(v, FILTER(lt, lt_id=v)))))),
key, SCAN("", CHOOSECOLS(merge1, 1), LAMBDA(a, v, IF(v="", a, v))),
merge2, HSTACK(merge1, VLOOKUP(key, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0)),
CHOOSECOLS(merge2, SEQUENCE(1, COLUMNS(merge2)-1, 2))
)
)Important: The join key must be unique in the right table. If your data contains duplicate keys in either table, see Google Sheets Joins with Duplicate IDs (Master Guide).
Sample Data
The example below uses two simple tables. The left table contains employee records, and the right table contains department details. Both tables share Department ID, which we’ll use as the join key.
To follow along, make a copy of the sample sheet below.
Right Join Formula
Enter the following formula in cell I1. It returns every row from the right table together with the matching employee records from the left table.
=ArrayFormula(
LET(
lt, A1:C,
lt_id, C1:C,
rt, E1:G,
rt_id, E1:E,
merge1, REDUCE(TOCOL(,1), TOCOL(rt_id, 1), LAMBDA(a, v, IFNA(VSTACK(a, HSTACK(v, FILTER(lt, lt_id=v)))))),
key, SCAN("", CHOOSECOLS(merge1, 1), LAMBDA(a, v, IF(v="", a, v))),
merge2, HSTACK(merge1, VLOOKUP(key, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0)),
CHOOSECOLS(merge2, SEQUENCE(1, COLUMNS(merge2)-1, 2))
)
)The formula returns the join key (Department ID) twice—once from the left table and once from the right table.
If you want to remove the duplicate key column, you can use either CHOOSECOLS or QUERY.
Remove the Duplicate Join Key with CHOOSECOLS
Replace the final expression:
CHOOSECOLS(merge2, SEQUENCE(1, COLUMNS(merge2)-1, 2))with:
CHOOSECOLS(
CHOOSECOLS(merge2, SEQUENCE(1, COLUMNS(merge2)-1, 2)),
1, 2, 4, 5, 6
)Remove the Duplicate Join Key with QUERY
Replace it with:
QUERY(
CHOOSECOLS(merge2, SEQUENCE(1, COLUMNS(merge2)-1, 2)),
"SELECT Col1, Col2, Col4, Col5, Col6",
1
)Note: Because this formula uses IFNA, dates, percentages, currencies, and other numeric values may lose their original number formatting. If this happens, reapply the appropriate format from Format → Number.
How to Adapt the Formula to Different Tables
To use this formula with your own data, you only need to update the four range assignments inside LET.
Update these four variables:
- 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 either table.
How the Formula Works
The formula builds the result in three stages.
1. Build the joined rows
merge1, REDUCE(TOCOL(,1), TOCOL(rt_id, 1), LAMBDA(a, v, IFNA(VSTACK(a, HSTACK(v, FILTER(lt, lt_id=v))))))For each key in the right table, FILTER returns the matching rows from the left table. HSTACK places the current key before the matching rows, and REDUCE iterates through all keys in the right table, stacking the results vertically to build the joined dataset.
2. Fill blank key cells
key, SCAN("", CHOOSECOLS(merge1, 1), LAMBDA(a, v, IF(v="", a, v)))The first column of merge1 contains the join key. When a key matches multiple rows in the left table, only the first row contains the key, while the following rows are blank. SCAN fills these blank cells with the most recent nonblank key above them.
3. Append the right table columns
merge2, HSTACK(merge1, VLOOKUP(key, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0))VLOOKUP retrieves the matching row from the right table for each key and HSTACK appends the corresponding columns to merge1.
Finally, the last expression returns the required columns from the joined result.
CHOOSECOLS(merge2, SEQUENCE(1, COLUMNS(merge2)-1, 2))This step removes the helper key column and returns the final right join output.
Frequently Asked Questions
Does Google Sheets have a RIGHT JOIN function?
No. Google Sheets doesn’t have a built-in RIGHT JOIN function. However, you can achieve the same result using the array formula in this tutorial.
Can I right join tables with duplicate IDs?
Yes, but it depends on which table contains the duplicate IDs. The formula in this tutorial handles duplicate IDs in the left table, but it assumes the join key is unique in the right table.
If your data contains duplicate IDs in the right table (or in both tables), see Google Sheets Joins with Duplicate IDs (Master Guide).
What’s the difference between LEFT JOIN and RIGHT JOIN?
A left join keeps all rows from the left table and adds matching rows from the right table. A right join does the opposite—it keeps all rows from the right table and adds matching rows from the left table.
Can I join more than two tables?
Not directly. This formula joins two tables at a time. If you need to join three or more tables, perform the joins in stages or use a different approach.
Conclusion
Although Google Sheets doesn’t have a built-in RIGHT JOIN function, you can recreate one with a single dynamic array formula. Once you understand the four input ranges inside LET, you can reuse the formula with almost any pair of related tables.
If you also need to perform left, inner, full, or anti joins, check out the related tutorials below.
