Guides › Documents & Data
Combine two spreadsheets that share a column
Match up two lists, such as customers and orders, by a shared column, with a formula you understand and a check for anything that didn't match.
When to use it
When information you need is split across two files
Step by step
Put both lists in one file
Copy each list into its own tab in the same spreadsheet. Make sure both have a column that matches, such as an email or order number.
Describe the match
Paste this prompt into your AI assistant, along with the header rows of each tab.
Prompt to copyI'm using [Excel / Google Sheets]. Tab '[tab 1 name]' has columns: [list]. Tab '[tab 2 name]' has columns: [list]. Both share [matching column]. I want to add [column(s) to bring across] from [tab 2] into [tab 1]. Give me: the formula (use XLOOKUP if available, otherwise VLOOKUP or INDEX/MATCH), which cell to put it in, how to fill it down, how to show 'Not found' instead of an error, and how to count how many rows didn't match. Explain each step simply.Fill it down and check
Add the formula, fill it down, then filter for 'Not found'. These are usually spelling differences or extra spaces in the matching column.
Fix the mismatches
Tidy the matching column (for example with TRIM or by fixing typos) and the formula will update automatically.
What you get
In Customers!E2: =XLOOKUP(B2, Orders!A:A, Orders!D:D, "Not found"). Fill down to row 540. Count of unmatched: =COUNTIF(E:E, "Not found") gives 17. Most were emails with trailing spaces or typos.
Tips
- Emails and codes are better matching columns than names, which are often spelled differently.