Guide · Spreadsheets
How to merge two spreadsheets in Excel and Google Sheets
You have two lists, say a customer list and an order export, or last year’s Christmas card list and this year’s, and you need them joined up. The trick is a shared column and one lookup formula. Here is the method that works, plus how to fix the errors that usually stop people.
Want it done for you? Spreadsheet clean-up from £15
Step 1: find the column both lists share
This is your key. Good keys are unique: email address, account number, order number, product code (SKU) or postcode plus house number. Names alone are risky because two people can share a name and spellings vary.
Step 2: clean the key column in both lists
- Remove stray spaces: in a spare column use
=TRIM(A2), then copy and paste as values. - Make numbers match numbers: codes like 00123 stored as text will not match 123 stored as a number. Format both columns the same way.
- Make emails lower case with
=LOWER(A2)so the lists agree. - Remove duplicates in the second list first (see our remove duplicates guide), otherwise a lookup only returns the first match.
Step 3: pull the data across with XLOOKUP
In Microsoft 365, Excel 2021 or Google Sheets, click the first empty column in list 1 and type:
=XLOOKUP(A2, Orders!A:A, Orders!C:C, "Not found")
That says: look for the value in A2 inside column A of the Orders sheet, and bring back what is in column C on the same row. If nothing matches, show "Not found" rather than an error. Fill the formula down. Repeat for each column you want to bring across.
Step 4: older Excel? Use VLOOKUP or INDEX MATCH
=VLOOKUP(A2, Orders!A:D, 3, FALSE) brings back the 3rd column of the range. The key must be in the first column of the range, and the FALSE at the end means exact match: always include it. If the column you want is to the left of the key, use =INDEX(Orders!B:B, MATCH(A2, Orders!D:D, 0)) instead.
Step 5: find what is missing from each list
Add a check column in list 1: =IF(ISNA(MATCH(A2, Orders!A:A, 0)), "Missing", "In both"). Filter on "Missing" to see customers with no order. Do the same the other way round in list 2 to find orders with no customer.
Step 6: freeze the result
Lookups break if someone deletes or sorts the other sheet. When you are happy, copy the new columns and paste them as values. Keep a copy of the two originals in case you need to check something later.
For big or repeat jobs: Power Query
In Excel on Windows, turn both lists into tables (Ctrl + T), then go to Data, Get Data, combine them with Merge Queries on the key column and choose a join type (all rows from the first list, matching rows from the second). Next month, drop in the new export and press Refresh. Microsoft explains the options on its merge queries help page.
Mistakes to avoid
- Leaving out FALSE (or 0) in VLOOKUP and MATCH. Without it Excel guesses the nearest value and quietly returns the wrong row.
- Matching on names only. Use an ID or email if you have one.
- Sorting one sheet while the formulas still point at it, then pasting values from the wrong state. Paste values first, then sort.
- Ignoring #N/A. Each one is either a cleaning problem or a genuinely missing row, and both matter.
Related guides: clean up a messy spreadsheet, split first and last names, convert a PDF to Excel and print Christmas card labels from Excel.
Frequently asked questions
- What is the easiest way to merge two spreadsheets?
- Pick one column that appears in both lists, such as an email address, order number or product code. Then use XLOOKUP (or VLOOKUP in older Excel) to pull the extra columns from the second list into the first.
- Why does my VLOOKUP show #N/A?
- Usually the value is not an exact match: an extra space, a number stored as text, different capitals in some systems, or the item really is missing. Clean both key columns with TRIM, make sure the types match, and use exact match (FALSE or 0).
- Should I use XLOOKUP or VLOOKUP?
- Use XLOOKUP if you have Microsoft 365, Excel 2021 or Google Sheets. It can look left, defaults to exact match and lets you set your own text when nothing is found. Use VLOOKUP or INDEX MATCH only for older versions of Excel.
- How do I find names that are in one list but not the other?
- Add a check column with =ISNA(MATCH(A2,Sheet2!A:A,0)). TRUE means the row is missing from the second list. Filter on TRUE to see them.
- Can I merge two sheets without formulas?
- Yes. In Excel, Power Query (Data, Get Data) can merge two tables on a shared column and refresh later. For a one-off job you can also copy one list under the other and then remove duplicates.
Want your two lists merged for you?
Send us both files and tell us which column should match. We clean the keys, merge the lists, flag every row that is missing from either side and remove duplicates, so you get one tidy Excel or Google Sheets file back by email. Up to 500 rows is £15 and up to 10,000 rows is £60, paid by a one-off card payment, with one round of changes included.