I was wondering what is the best way to merge two spreadsheets which have the name id's e.g:

One sheet has:

apple   5
dog     3
cat     2

Second sheet:

dog     4
apple   7
cat     3

I would like to merge the two so that I have

apple   5    7
dog     4    3
cat     2    3

They're not in order, and sorting them in Excel didn't help to fix the problem.


This is easiest done in a database using Joins, but you can do it in excel using VLOOKUP.

First create a third sheet, and paste the 'dog, cat, etc' columns from sheet one and two into a single column in sheet 3. The use Excel 2007's Remove Duplicates feature to remove duplicates.

Then in the second column, specify the formula =VLOOKUP(A1, Sheet1!A$1:B$100, 2, false) where A1 is the cell to the left of the cell youu're editing (on sheet 3) and A1:B100 is the region on sheet 1 containing data.

In the third column you specify a similar formula, but pointing to Sheet 2, =VLOOKUP(A1, Sheet2!A$1:B$100, 2, false).

You may need to sort the data in sheet 1 and 2, and the names on sheet 3.

  • If you’re using an Excel version before 2007 "Remove Duplicates" is not available, but you can achieve the same effect from the Data -> Filter -> Advanced Filter control by getting it to copying to a new location and ticking "Unique records only". – DMA57361 Jan 24 '11 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.