You can use Left, Right, Inner, and Full Joins in Google Sheets to combine two related tables based on a common key column. These joins are useful when related data is stored in separate tables. The combined table makes it easier to analyze the data and generate meaningful results.

The QUERY function in Google Sheets can’t perform true Left, Right, Inner, or Full Joins. Although its syntax is similar to SQL, it doesn’t support all SQL features and has its own limitations. So, what are the alternatives?

When the join key contains unique IDs, simple lookup functions or basic array formulas work well. However, they don’t handle duplicate IDs correctly because they return only the first matching record.

The formulas in this tutorial work whether the key column contains unique IDs, duplicate IDs, or a mix of both.

If your tables contain only unique IDs, check out these simpler guides for each join type:

• Left Join Two Tables in Google Sheets (Array Formula)
• Right Join Two Tables in Google Sheets (Array Formula)
• Inner Join Two Tables in Google Sheets (Array Formula)
• Full Join Two Tables in Google Sheets (Array Formula)

Each tutorial focuses on a different way of matching and combining rows from two tables.

If either table contains duplicate IDs, read on. This tutorial explains how to use dynamic array formulas to perform Left, Right, Inner, and Full Joins correctly.

Left Join with Duplicates

A left join keeps every row from the left table and adds matching data from the right table whenever it’s available.

Syntax

=ArrayFormula(
  LET(
    lt, left_table_range, ltIdCol, left_table_key_column_index, ltId, CHOOSECOLS(lt, ltIdCol),
    rt, right_table_range, rtIdCol, right_table_key_column_index, rtId, CHOOSECOLS(rt, rtIdCol),
    ltF, FILTER(lt, ltId<>""), ltIdF, FILTER(ltId, ltId<>""),

    lj,
    IFNA(
      REDUCE(
        TOCOL(,1), SEQUENCE(ROWS(ltIdF)),
        LAMBDA(acc, val,
          LET(
            id, CHOOSECOLS(ltF, ltIdCol), data, ltF,
            VSTACK(
              acc,
              IFNA(
                HSTACK(INDEX(data, val), IFNA(FILTER(rt, rtId=INDEX(id, val)))),
                INDEX(data, val)
              )
            )
          )
        )
      )
    ),

    lj
  )
)

Replace the placeholders in the formula as follows:

In the following example, replace the placeholders with:

• left_table_range → A1:C
• right_table_range → E1:F
• left_table_key_column_index → 1
• right_table_key_column_index → 1

Example

The Student Course Table is the left table, and the Student Activity Table is the right table. The common key column is Student ID.

The formula returns every row from the Student Course Table and appends the matching columns from the Student Activity Table. It correctly returns all matching rows, even when the Student ID column contains duplicate values.

Note: The second Student ID column comes from the right table. You can hide the column if you don’t need it. To remove it from the formula output, wrap the formula with CHOOSECOLS and select only the columns you want to return.

Right Join with Duplicates

Right join keeps every row from the right table and adds matching data from the left table whenever it’s available.

Syntax

=ArrayFormula(
  LET(
    lt, left_table_range, ltIdCol, left_table_key_column_index, ltId, CHOOSECOLS(lt, ltIdCol),
    rt, right_table_range, rtIdCol, right_table_key_column_index, rtId, CHOOSECOLS(rt, rtIdCol),
    rtF, FILTER(rt, rtId<>""), rtIdF, FILTER(rtId, rtId<>""),

    rj,
    IFNA(
      REDUCE(
        TOCOL(,1), SEQUENCE(ROWS(rtIdF)),
        LAMBDA(acc, val,
          LET(
            id, CHOOSECOLS(rtF, rtIdCol), data, rtF,
            VSTACK(
              acc,
              IFNA(
                HSTACK(INDEX(data, val), IFNA(FILTER(lt, ltId=INDEX(id, val)))),
                INDEX(data, val)
              )
            )
          )
        )
      )
    ),

    CHOOSECOLS(
      rj,
      VSTACK(
        SEQUENCE(COLUMNS(lt), 1, COLUMNS(rt)+1),
        SEQUENCE(COLUMNS(rt))
      )
    )
  )
)

Example

Using the same sample data:

The formula returns every row from the Student Activity Table and prepends the matching student and course details from the Student Course Table.

Inner Join with Duplicates

Inner join keeps only the rows that have matching values in both tables.

Syntax

=ArrayFormula(
  LET(
    lt, left_table_range, ltIdCol, left_table_key_column_index, ltId, CHOOSECOLS(lt, ltIdCol),
    rt, right_table_range, rtIdCol, right_table_key_column_index, rtId, CHOOSECOLS(rt, rtIdCol),
    ltF, FILTER(lt, ltId<>""), ltIdF, FILTER(ltId, ltId<>""),

    lj,
    IFNA(
      REDUCE(
        TOCOL(,1), SEQUENCE(ROWS(ltIdF)),
        LAMBDA(acc, val,
          LET(
            id, CHOOSECOLS(ltF, ltIdCol), data, ltF,
            VSTACK(
              acc,
              IFNA(
                HSTACK(INDEX(data, val), IFNA(FILTER(rt, rtId=INDEX(id, val)))),
                INDEX(data, val)
              )
            )
          )
        )
      )
    ),

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

Example

Using the same sample data:

The formula returns only the rows with matching Student ID values in both tables.

Full Join with Duplicates

Full join keeps every row from both tables, matching related rows and including non-matching rows from either table.

Syntax

=ArrayFormula(
  LET(
    lt, left_table_range, ltIdCol, left_table_key_column_index, ltId, CHOOSECOLS(lt, ltIdCol),
    rt, right_table_range, rtIdCol, right_table_key_column_index, rtId, CHOOSECOLS(rt, rtIdCol),
    ltF, FILTER(lt, ltId<>""), ltIdF, FILTER(ltId, ltId<>""),

    mm, FILTER(rtId, NOT(ISNUMBER(XMATCH(rtId, ltIdF))), rtId<>""),
    mmI, IF(ltIdCol=1, mm, HSTACK(WRAPROWS(, ltIdCol-1), mm)),
    ltFmmI, VSTACK(ltF, mmI),

    fj,
    IFNA(
      REDUCE(
        TOCOL(,1), SEQUENCE(ROWS(ltFmmI)),
        LAMBDA(acc, val,
          LET(
            id, CHOOSECOLS(ltFmmI, ltIdCol), data, ltFmmI,
            VSTACK(
              acc,
              IFNA(
                HSTACK(
                  INDEX(data, val),
                  IFNA(FILTER(rt, rtId=INDEX(id, val)))
                ),
                INDEX(data, val)
              )
            )
          )
        )
      )
    ),

    fj
  )
)

Example

Using the same sample data:

The formula returns all rows from both tables, combining rows with identical Student IDs where a match exists.

Sample Sheet

Make a copy of the sample Google Sheets file to see all four join formulas in action.

Copy Sample Sheet

To keep the formulas easy to follow, each join type is demonstrated on a separate worksheet.

Conclusion

One advantage of these join formulas is that the common key column can be anywhere in each table. It can be the first column, second column, or any other column, and it doesn’t have to be in the same position in both tables.

The only requirement is that both tables use the same header for the key column. Otherwise, the formula may not return the header row as expected.

Joining two tables on a common key column is no longer a difficult task, even when one or both tables contain duplicate IDs. Simply choose the join formula that best fits your needs.

I’ve tested all the formulas with the sample data in this tutorial. If you run into any issues with your own dataset, feel free to post them in the comments.