Compare Columns and Copy if match found

**betsyroal** · 11-06-2010, 06:57 AM

I have two huge excel sheets that I have to compare. I'm checking ID in one sheet with ID in another sheet. If the IDS match then I need to copy the name that correspond with that ID to the other sheet that don't contain the name.

Let me see if I can explain in excel terms:

I'm trying to compare two columns from two separate sheets in the same workbook. If there is a match, I want to copy a the value from that row but different column where the match was found from one of the sheets to the next. I'm using excel 2003. Example..If Sheet1 Col A2 is equal to Sheet2 Col B (search the entire column for match) then copy Sheet 2 Col C (whatever row the match was found)to Sheet 1 Col B2. Continue checking all the values in Sheet 1 Col A until there are no more cells to check.

Any help would be appreciated!

Thanks in advance.

**mrice** · 11-06-2010, 07:00 AM

Sounds like you ought to investigate Advanced filter where you use the IDs in the second sheet as the criteria. See Excel help for details.

**trenchant** · 11-22-2010, 05:41 PM

vvvvvvvvvv

**enimal** · 04-29-2014, 07:58 AM

I would suggest following options on how to combine worksheets:

1. Creating a query from Excel file
- first you would define names for both of the datasets (Formulas tab -> Define name)
- then goto Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
- select your workbook file and confirm that you want to merge the columns manually
- in the following window "Query from Excel Files", drag&drop the column of first dataset into the column of second dataset - a link between these columns will be created
- go to File menu, click "Return Data to MS Office Excel", an Import Data dialog will pop up
- Select the sheet into which you would like the matched data to be imported
- Click OK -> you should see matched data

For a step-by-step video guide, checkout https://www.youtube.com/watch?v=0uWjXuhvqB8

2. If your spreadsheets use specific structure, you can try INDEX MATCH or VLOOKUP functions to lookup and match values in certain data ranges. You'll find plenty of resources on this topic, including official MS Office documentation.

Hope this helps.