Analistable

How to link two Google Sheets

By the Analistable team · Updated · 5 min read

To show data from another tab, use ='Tab name'!A1. To bring data from another file, use IMPORTRANGE. To join two sheets — add each customer's details next to their orders — match on a shared ID: =XLOOKUP(A2, Customers!A:A, Customers!B:B, "Not found"), or fill a whole column at once with =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, Customers!A:C, 2, FALSE), "")).

Part of our guide: How to combine data in Google Sheets

Three kinds of link

Linking options in Google Sheets
NeedFormula
One cell from another tab='Customers'!B2
A range from another file=IMPORTRANGE("url", "Customers!A1:C")
Match rows by ID=XLOOKUP(A2, Customers!A:A, Customers!B:B, "Not found")

Join two tabs by ID

Orders tab: Order ID, Customer ID, Amount. Customers tab: Customer ID, Name, Country. In Orders, D2:

=ARRAYFORMULA(IF(B2:B="", , IFERROR(VLOOKUP(B2:B, Customers!A:C, {2, 3}, FALSE), "Not found")))

This fills Name and Country for every order in one formula. {2, 3} returns the second and third columns of the Customers range.

Orders with customer details
Order IDCustomer IDAmountNameCountry
O-9001C-101120Bakery LuneFrance
O-9002C-10375Nordic SupplyDenmark
O-9004C-10460Not found

Join two separate files

  1. In the Orders file, add a tab called Customers_import.
  2. In A1, enter =IMPORTRANGE("customers file URL", "Customers!A1:C") and allow access.
  3. Use the lookup above against Customers_import.

Importing once and looking up locally is faster than putting IMPORTRANGE inside every lookup.

Limits of lookups

  • One match per row: if a customer appears twice in Customers, only the first row is used.
  • One direction: to list all orders per customer, use =TEXTJOIN(", ", TRUE, FILTER(Orders!A:A, Orders!B:B=A2)) — see TEXTJOIN for matched rows.
  • QUERY can't join two ranges, so lookups are the way to combine tables in Sheets.

Linking Google Sheets with Excel files too? Analistable connects both and joins them on the column you confirm. See joining spreadsheets for the concepts.

XLOOKUP vs VLOOKUP in Sheets

Lookup functions for linking sheets
VLOOKUPXLOOKUP
Look left of the keyNoYes
Default matchApproximate unless FALSEExact
Not-found valueWrap in IFERRORBuilt in
Fill a column with ARRAYFORMULAYesYes

Either works for linking; XLOOKUP is harder to get wrong. See VLOOKUP vs XLOOKUP vs INDEX MATCH.

Check the link

Count the rows that didn't find a match with =COUNTIF(D2:D, "Not found"). If it's high, compare a few IDs by eye: spaces, prefixes (“C-101” vs “101”) and numbers stored as text are the usual causes. Clean with TRIM or a helper column before linking.

Step by step: link two files by customer ID

  1. Open the Customers file and copy its URL from the address bar.
  2. In the Orders file, add a tab called Customers_import.
  3. In A1 of that tab, enter =IMPORTRANGE("paste-the-URL", "Customers!A1:C") and click Allow access.
  4. Back on the Orders tab, enter the lookup in D2: =ARRAYFORMULA(IF(B2:B="", , IFERROR(VLOOKUP(B2:B, Customers_import!A:C, {2, 3}, FALSE), "Not found"))).
  5. Add headers Name and Country in D1 and E1.
  6. Hide the Customers_import tab if you don't want people to edit around it.

New customers added to the Customers file appear in Customers_import automatically, and any order using their ID picks up the name and country without further edits.

Link by two columns

Some records need two fields to identify them — for example a product code plus a size. Join the two into one key on both sides, inside the formula:

=ARRAYFORMULA(IF(A2:A="", , IFERROR(
  VLOOKUP(A2:A&"|"&B2:B, {Prices!A2:A&"|"&Prices!B2:B, Prices!C2:C}, 2, FALSE),
  "Not found")))

The {…, …} array builds a temporary two-column table whose first column is code|size, so VLOOKUP can match the combined key. The “|” separator stops “AB” + “12” from matching “A” + “B12”.

Return every match, not just the first

Lookups stop at the first matching row. If one customer has several contacts and you want all of them listed, FILTER returns every matching row:

=FILTER(Contacts!B2:C, Contacts!A2:A = A2)

FILTER spills the results downward, so it suits a detail view for one ID. For one row per ID with the values joined in a single cell, use TEXTJOIN around FILTER as shown above.

Worked example: what the Not found rows tell you

In the Orders example, order O-9004 uses customer ID C-104, which isn't in the Customers tab. That is either a missing customer record or a typo in the order. Over a full month, the counts make the scale clear:

Link quality for September orders
CheckFormulaResult
Orders=COUNTA(A2:A)240
Linked to a customer=COUNTA(A2:A) - COUNTIF(D2:D, "Not found")234
Not found=COUNTIF(D2:D, "Not found")6
Match rate=1 - 6/24097.5%

With 6 of 240 orders unmatched, 234 (97.5%) are linked. Fix the six IDs at the source rather than overriding the formula results.

Troubleshooting

When linked values are wrong or missing
SymptomCauseFix
Every row says Not foundLookup range doesn't start at the ID columnVLOOKUP searches the first column of the range
Wrong customer returnedFALSE omitted, so approximate match was usedAdd FALSE as the last argument
IDs that look equal don't matchNumber in one tab, text in the otherWrap both sides in TO_TEXT() or use &""
#REF! in the import tabAccess not yet allowedClick the cell and allow access

Keep linked values when the source is deleted

A link is only as stable as its source: if someone deletes a customer row, past orders lose their name. For records that must not change, such as invoiced orders, freeze the result once a month. Select the linked columns, copy, and use Edit → Paste special → Values only on a separate archive tab. Keep the live formulas on the working tab for new orders.

Frequently asked questions

How do I link data between two Google Sheets?
Use ='Tab'!A1 for another tab, IMPORTRANGE for another file, and XLOOKUP or VLOOKUP to match rows by a shared ID.
Can I join two tables in Google Sheets?
Not with QUERY. Use VLOOKUP or XLOOKUP inside ARRAYFORMULA to bring columns from one table into another.
Does a linked sheet update automatically?
Yes. References, IMPORTRANGE and lookups recalculate when the source changes.
Why does my linked column show Not found for IDs that exist?
Usually one side stores the ID as a number and the other as text, or one has extra spaces. Clean both with TRIM and TO_TEXT before looking up.

Related guides