In Google Sheets, a right join combines data from two tables by keeping all records from the right table and including matching records from the left table. This join ensures that all data from the right table is preserved, even if certain values lack corresponding entries in the left table.
To effectively perform a right join, the right table should have unique values in the common field. This precaution helps prevent ambiguity and ensures accurate data merging.
Note: If you have duplicated IDs in both ID columns (left and right tables), please refer to our guide on conquering duplicate IDs to master left, right, inner, and full joins in Google Sheets.
Right Join Example: Departments and Employees
Let’s explore a scenario where we perform a right join between two tables. The left table contains departments, and the right table contains employee names and their date of joining.
Table 1: Departments
| Emp ID | Department |
| —— | ———- |
Table 2: Employees
| Emp ID | Emp Name | DOJ |
The Employee ID serves as the common field in both tables, enabling a right join. Importantly, the right table has unique records for employee IDs.
The result of the right join would be a combined table containing all employee records from the right table, along with their corresponding department information (if available) from the left table.
Expected Result:
| Emp ID | Department | Emp ID | Emp Name | DOJ |
To see the screenshot of the above tables in Google Sheets, please scroll down.
Performing a Right Join in Google Sheets
To execute a right join on two tables in Google Sheets, you can use a complex formula involving several functions. However, you only need to specify the table ranges and unique identifier column ranges, and the formula will handle the rest.
Here’s the formula for performing a right join on two tables, assuming the right table has unique records in Google Sheets:
=ArrayFormula( LET( lt, A2:B8, lt_id, A2:A8, rt, D2:F7, rt_id, D2:D7, merge1, REDUCE("", rt_id, 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)), CHOOSEROWS(CHOOSECOLS(merge2, SEQUENCE(1, COLUMNS(merge2)-1, 2)), SEQUENCE(ROWS(key)-1, 1, 2)) ) )After performing the right join, you may need to apply ‘Format > Number > Date’ to any column that contains date values. You can find the right join array formula in cell A12 of the third sheet in the provided sample sheet below.
Anatomy of the Right Join Formula
The right join formula utilizes the LET function to assign values to specific expressions and returns the resulting table from the right join.
Syntax of the LET Function:
LET(name1, value_expression1, [name2, ...], [value_expression2, ...], formula_expression)LET Assignments
lt: Defines the data range of the left table, including the header row.lt_id: Defines the ID column of the left table, including the header row.rt: Defines the data range of the right table, including the header row.rt_id: Defines the ID column of the right table, including the header row.
Other LET Assignments
merge1: Filters the rows in the left table that match the Emp ID in the right table and horizontally stacks them.key: Fills blank cells in the first column of merge1 with the values from the cell above.merge2: Combines the merge1 table with the results of a VLOOKUP lookup based on the key value.
To better understand the formula and its intricate parts, we recommend referring to the related resources provided below.
Sample Sheet and Result
To see the tables used in the right join example and the resulting merged table, check out our sample sheet here.
Conclusion
Performing a right join on two tables in Google Sheets can be achieved using a complex formula. By following the steps outlined in this article, you’ll be able to combine data from the right table while preserving all records, even if they lack corresponding entries in the left table.
For more information on other types of joins, including left join, inner join, and full join, be sure to check out our related resources.
Happy joining!
