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
| Need | Formula |
|---|---|
| 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.
| Order ID | Customer ID | Amount | Name | Country |
|---|---|---|---|---|
| O-9001 | C-101 | 120 | Bakery Lune | France |
| O-9002 | C-103 | 75 | Nordic Supply | Denmark |
| O-9004 | C-104 | 60 | Not found |
Join two separate files
- In the Orders file, add a tab called Customers_import.
- In A1, enter
=IMPORTRANGE("customers file URL", "Customers!A1:C")and allow access. - 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
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Look left of the key | No | Yes |
| Default match | Approximate unless FALSE | Exact |
| Not-found value | Wrap in IFERROR | Built in |
| Fill a column with ARRAYFORMULA | Yes | Yes |
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
- Open the Customers file and copy its URL from the address bar.
- In the Orders file, add a tab called Customers_import.
- In A1 of that tab, enter
=IMPORTRANGE("paste-the-URL", "Customers!A1:C")and click Allow access. - 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"))). - Add headers Name and Country in D1 and E1.
- 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:
| Check | Formula | Result |
|---|---|---|
| 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/240 | 97.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
| Symptom | Cause | Fix |
|---|---|---|
| Every row says Not found | Lookup range doesn't start at the ID column | VLOOKUP searches the first column of the range |
| Wrong customer returned | FALSE omitted, so approximate match was used | Add FALSE as the last argument |
| IDs that look equal don't match | Number in one tab, text in the other | Wrap both sides in TO_TEXT() or use &"" |
| #REF! in the import tab | Access not yet allowed | Click 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.