You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- merging workbooks

Advertisement

Hi Tom,

I have two excel workbooks with data on the same students as shown in attached image 1.

I want to merge both workbooks to create a more useful workbook for me as shown in image 2.

I figure the unique number assigned to each student is the correlating factor but not sure how to do the rest.

If you are able to help me out it would be very much appreciated and save me hours.

Thanks very much in advance.

Charlie

Charlie,

You show workbook1 and workbook2. Generally data is in a sheet in a workbook and to give a proper example, the formula would need to refer to the sheet.

so assume Workbook1 is actually [Workbook1.xlsx]Sheet1

and Workbook2 is actually [Workbook2.xlsx]Sheet2

so assume you copy the "number" column from one of the worksheets and paste into column A of another worksheet.

then in B2 you could do this

=IfError(Vlookup($A2,[Workbook1.xlsx]Sheet1!$A:$G,3,False),"")

if either the workbook1 actual name or the sheet1 actual name had a space in it then you would need to enclose in single quotes like this:

=IfError(Vlookup($A2,'[Workbook 1.xlsx]Sheet 1'!$A:$G,3,False),"")

Vlookup depends on the leftmost column in the lookup range to contain the value being looked up. In this case "Number". the third argument to Vlookup, the 3 in the example tells what column from the lookup range to return the value from the matched row. Since we want the value from column C of workbook1, and we are looking in A to G, we put in the argument 3

So in column G of the new sheet, you would want to look up Class from Workbook2. Then the formula would be

=IfError(Vlookup($A2,[Workbook2.xlsx]Sheet1!$B:$F,5,False),"")

So we look in column B on Workbook2 for the match and return the value from column F which is the 6th column in the sheet, but the 5th column in the lookup range since we start in column B.

If you needed to retrieve a value from column A of workbook2 since it is to the left of the number column you would use index and match

=Index([Workbook2.xlsx]Sheet1!$A:$A,Match($A2,[Workbook2.xlsx]Sheet1!$B:$B,0),1)

Hopefully that gives you enough information to resolve your problem.

Note that if all the sheets are in the same workbook, then you would only need to use the sheet names:

Assume workbook2 is actually named Sheet2

=IfError(Vlookup($A2,Sheet2!$B:$F,5,False),"")

--

Regards,

Tom Ogilvy

=IfError(Vlookup($A2,[Workbook1.xlsx]Sheet1!A:G,3,False),"")

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.