Merging Excel Worksheets or Workbooks

Using VLOOKUP, or OFFSET and MATCH Recently, I was working in Panama with a client implementing Lean Six Sigma. One of the participants had an unusual problem. She needed to integrate four different reports into one so that she could analyze several of their key process indicators (KPIs). Unfortunately, Excel doesn't provide an easy way to merge workbooks or worksheets based on a key. We could have exported the data into Access and merged them that way, but there's an easier way to do it right in Excel using VLOOKUP or OFFSET and MATCH functions.

Merge Excel Worksheets with VLOOKUP

I've simplified the data to make the process easier to understand. It's pretty simple:
  1. Put all of the worksheets to be merged into one workbook. The 3 worksheets in this example are named - Name, Address and Products.
  2. name worksheets
  3. Sort each worksheet in ascending order on its database "key". The key is the common field contained in each worksheet. In the Name and Address worksheets, the database key is the Record ID # in column A.
  4. In a new worksheet, enter the formulas to "look up" the database "key" in each worksheet.
Here are the simplified worksheets for this example: 

Name
sample sheet 1 


Address
sample sheet 2 

Products
sample sheet 3 


I want to merge name and address based on the common field "the ID", and then merge the product name in Products column B based on the product in the "name" worksheet column C. 

I can use the VLOOKUP function to do this. 

I start by creating a new worksheet and entering the headings in row 1 and the record IDs in column A. Then I put my cursor in cell B2 to build the VLOOKUP formula. 

output sheet 

Click on the Insert function key and then find VLOOKUP under Lookup and Reference. Excel's function window will help you through this. Here is a brief explanation of each part of the formula:
  • Lookup value - location of the value (the database key) I want to look up. Cell A2 in my example.
  • Table Array - the worksheet and cells I want to look in to find the data I want to retrieve
  • Col Index Num - the column in the Table Array that I want to get the data from.
  • Range Lookup - select True for the closet match and False for an exact match.
In English my formula says: lookup the value in A2 (which is 1) in cells A1 to C4 of the worksheet called Name. When you find a match, return the corresponding value in column 2 (John Brown). 

vlookup function arguments 

Here is my final populated spreadsheet
merge excel worksheets with VLOOKUP 

The formulas to look up each field are:
  • Customer name in the name worksheet: =VLOOKUP(A2,Name!A$1:C$4,2,TRUE)
  • Customer address: =VLOOKUP(A2,Address!A$1:B$4,2,TRUE)
  • Product Ordered: =VLOOKUP(A2,Name!A$1:C$4,3,TRUE)
  • Product Name: =VLOOKUP(D2,Products!A$1:B$3,2,TRUE)

Merging Worksheets with OFFSET and MATCH

The process is the same except you don't have to sort the data:
  1. Put all of the worksheets to be merged into one workbook.
  2. In a new worksheet, enter the formulas to "look up" the record IDs in each worksheet.
MATCH will look for an exact match in the data, regardless of sorting. For example: 

MATCH($A2,Name!$A$2:$A$4,0) will give us a "1" which is the first row of A2:A4. 

OFFSET will use the index returned by MATCH to pick the correct field. For example: 

OFFSET($A1,row,col,height,width) using 1 for row, 1 for col,1 for height, and 1 for width would offset from A1 by one row down, one column over for a height and width of one cell. 

OFFSET ($A1,1,1,1,1) would select cell B2 (John Brown). 

If we replace the "1" for the row with the MATCH formula (yes, you can "nest" formulas inside of other formulas), we get the desired merged cell: 

Merge Excel Worksheets with OFFSET and MATCH 

The formulas to look up each field are:
  • Customer name in the name worksheet:
    =OFFSET(Name!$A$1,MATCH($A2,Name!$A$2:$A$4,0),1,1,1)
  • Customer address: 
    =OFFSET(Address!A$1,MATCH(A2,Address!A$2:A$4,0),1,1,1)
  • Product Ordered: 
    =OFFSET(Name!$A$1,MATCH($A2,Name!$A$2:$A$4,0),2,1,1)
  • Product Name: 
    =OFFSET(Products!$A$1,MATCH($D2,Products!$A$2:$A$4,0),1,1,1)

Download an Excel workbook with these worksheets and formulas.

Next Steps

Once all of the data is combined onto one worksheet, it becomes much easier to use pivot tables to summarize the data and create an improvement story using QI Macros. 

Using VLOOKUP or OFFSET/MATCH isn't as easy as it could be, but it's a lot easier than trying to combine the data manually. 

What data do you need to combine to perform a better, more detailed and robust analysis?


Stop using old technology!

Upgrade Your Excel and Data Analysis Skills to Smart Charts Using QI Macros.

Track Data Over Time

Primitive Chart
line graph
Line Graph
Smart Chart
control chart
Control Chart

Compare Categories

Primitive Chart
pie chart
Pie Chart
Smart Chart
pareto chart
Pareto Chart

Analyze Variation

Primitive Chart
bar or column chart
Bar or Column Chart
Smart Chart
histogram
Histogram

QI Macros add-in for Excel makes creating smart charts a snap.