Inner Join Two Tables in Google Sheets (Array Formula)

An inner join returns only the rows with matching values in the common key column of both tables. In Google Sheets, you can perform an inner join using a single array formula based on a shared key such as an employee ID, customer ID, account number, or student ID.

It’s one of the most common ways to combine related datasets because it keeps only the records that exist in both tables.

Table of Contents

Prerequisites

Before using the formula, ensure the following:

Both tables share a common key column.

Google Sheets Inner 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, VLOOKUP(lt_id, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0),
    merge, HSTACK(lt, look_up),
    FILTER(merge, CHOOSECOLS(look_up, 1)<>"")
  )
)

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

Sample Data

The sample data contains an Employees table on the left and a Departments table on the right. The tables are joined using the Department ID column.

Sample Employees and Departments tables used to demonstrate an inner join in Google Sheets

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

Sample Sheet

Inner Join Formula

To perform an inner join, enter the following array formula in cell I1:

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

The formula matches each Department ID in the Employees table with the corresponding row in the Departments table and returns only the matching records.

Result of an inner join in Google Sheets showing matching employee and department records

Remove the Duplicate Join Key Column

The joined result includes the Department ID column from both tables, resulting in two Department ID columns. In most cases, you’ll only need one of them. In most cases, you’ll only need one of them.

To do so, replace the following expression:

FILTER(merge, CHOOSECOLS(look_up, 1)<>"")

with one of the following alternatives.

Using CHOOSECOLS

CHOOSECOLS(
  FILTER(merge, CHOOSECOLS(look_up, 1)<>""),
  1, 2, 3, 5, 6
)

Using QUERY

QUERY(
  FILTER(merge, CHOOSECOLS(look_up, 1)<>""),
  "SELECT Col1, Col2, Col3, Col5, Col6",
  1
)

Both formulas remove the duplicate Department ID column while preserving the rest of the joined data.

Customize the Formula for Your Data

Adapting the formula to your own data is straightforward. You only need to update these four variables:

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

In most cases, these are the only parts of the formula you’ll need to change. The rest of the formula works without changes, regardless of how many columns either table contains.

You can include or exclude the header row in the ranges. If you include the header row, ensure the join key has the same column header in both tables so the header row is retained in the joined result.

Note: The joined table may lose number formatting such as dates, currencies, and percentages. If this happens, select the affected columns and reapply the appropriate format from Format → Number.

Formula Logic & Explanation

The formula performs the inner join in three steps using VLOOKUP, HSTACK, and FILTER.

VLOOKUP searches for each join key from the left table in the right table and returns the matching records.
HSTACK combines the left table with the lookup results.
FILTER removes rows that don’t have a matching record in the right table, leaving only the inner join result.

Lookup Matching Rows

look_up, VLOOKUP(lt_id, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0)

Searches for each join key from the left table in the right table and returns the matching records.

Combine Both Tables

merge, HSTACK(lt, look_up)

Horizontally combines the left table with the matching rows returned by VLOOKUP.

Keep Only Matching Records

FILTER(merge, CHOOSECOLS(look_up, 1)<>"")

Removes rows where VLOOKUP doesn’t find a matching key in the right table. In the filter condition, those unmatched results are treated as empty values, so only matching records are returned.

Frequently Asked Questions

What is an inner join in Google Sheets?

An inner join combines two tables based on a common key column and returns only the rows with matching key values in both tables.

Does Google Sheets have an INNER JOIN function?

No. Google Sheets doesn’t have a built-in INNER JOIN function. However, you can achieve the same result using an array formula like the one in this tutorial.

Can I use the QUERY function for an inner join?

No. The QUERY function cannot perform a true inner join between two separate tables. You can, however, use QUERY after joining the tables to select, reorder, or filter the joined columns.

Conclusion

You can now perform an inner join between two tables in Google Sheets using a single array formula. By assigning meaningful variable names with LET, the formula is easier to read, understand, and customize for your own data.

In most cases, you only need to update these four variables:

lt – Left table range
lt_id – Left table key column
rt – Right table range
rt_id – Right table key column

The rest of the formula works automatically, regardless of the number of columns in either table.

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