INTRODUCTION

In this article, we will show you simple and easy method to combine two excel workbook in excel. Read more to find out how

If you have 2 workbooks which you would like to combine, VLOOKUP function is your best friend in this case. Especially, it is extremely useful when one of the lists is not full and simply copying and pasting will not work.

As you can see on the picture workbooks have a different number of filled with text rows, due to the lack of some pieces of information on the right one. So, what to do?

Figure 1

STEP 2. MOOVE BOTH SHEETS TO ONE WORKBOOK

To make the process easier and faster you should work with both sheets in one workbook. To do it:

1. In your second workbook go to the “Home” tab.
2. Click on the “Format” button. The drop list will appear.

Figure 2

Figure 3

1. Click on the “Move or Copy Sheet…” option. The dialog box will appear.
2. Put a check in the “Create a copy” box.
3. Open the drop list to choose the book where you want to copy the worksheet. In this case, it is Workbook 1.
4. Depending on how many worksheets you have in the destination workbook choose where to place the new worksheet.

Figure 4

Figure 5

Figure 6

After clicking the OK, you suppose to have both sheets in your main workbook.

STEP 2. COPY & PASTE THE MISSING TABLE LABELS

The first table missing some columns. To fix this simply copy and paste the missing column

Figure 7

STEP 3. CHOOSE UNIQUE IDENTIFYING COLUMN THAT BOTH WORKSHEETS CONTAIN

Choose the column which has a unique identifier (every row in this column has an exclusive record).

In this case it will be NAME column, however, it could be an ID NUMBER or a PHONE NUMBER columns.

Figure 8

STEP 4. TIME TO USE THE VLOOKUP

Figure 9

Insert a VLOOKUP function in the first cell of the new column (the column which doesn’t have information), by:

1. Typing =VLOOKUP and pressing Tab, then Ctrl+A to open the Function Arguments dialog box

OR

1. Going to the Formulas tab Lookup & Reference VLOOKUP

Both ways will bring you to the Function Arguments dialog box

VLOOKUP function has 4 arguments:

Figure 10

1. Lookup_value – the value that you are looking for
2. Table_array – the range where you are looking for the wanted value
3. Col_index_num – the column number in the lookup range containing the return value
4. Range_lookup – has 2 possible values:
   • 0 / FALSE – will search for the Exact match with a Lookup_value
   • 1 / TRUE – will search for the Approximate match.

Our Lookup_value will be the content of our unique identifying column (watch STEP 3), that is NAME column. Put your cursor in the Lookup_value field, then click on the first cell in your unique identifying column.

Since you will copy this formula across, the column value should be locked, with an absolute reference. Lock it by pressing F4 three times or by typing $ before the cell column. This means that the row can be changed but you will always refer to column A.

Figure 11

We will look for the values on the other worksheet, therefore to set the Table_array we have to refer to the range on that worksheet.

To make your life easier you can define a name for a lookup range:

1. Got to the worksheet where you will seek for values
2. Select the range where you will seek for values (do not include headers)

Figure 12

1. Go to the Formulas tab and click the Define Name button. The dialog box will open.

Figure 13

1. In the dialog box insert a name for the range. You can use any name you want to. In this case, we use “Table2”.

Figure 14

After you click OK the range will have a defined name.

Now we can use this name as the value for the Table_array field. Just print the defined name and Excel will automatically find a range.

To set the Col_index_num you have to know the column number of values you want to get after the lookup. In this case, we are looking for emails, we want to fill the EMAIL column. When we look at the original worksheet, we see that the EMAIL column is 4^th from the left. Thus, we put 4 in the Col_index_num field.

Figure 15

Since our Lookup_value is the name, we need an Exact match. To look for exact matches type FALSE in the Range_lookup field.

After you filled all the fields click OK. Once you click it Excel will fill all the column automatically.

Figure 16

STEP 5. FACING PROBLEMS

However, sometimes when the original list is not full. You can get this result:

Figure 17

This means that Excel didn’t find the lookup value in the lookup range. Starting from Excel 2007, this problems can be fixed by using IFERROR function.

Figure 18

This function needs 2 arguments:

1. Value – argument that will be checked for an error.
2. Value_if_error – value to return if the error occurred.

To fix our problem we have to modify the formula for VLOOKUP.

The original formula looks like this in the Formula Bar:

Figure 19

We will use it as the Value argument in the IFERROR function. To do it:

1. Type IFERROR( before the VLOOKUP. This will make the VLOOKUP function an argument in the IFERROR function.

Figure 20

1. Put the comma after the last bracket and type “ “. You should put space between quotation marks because we want to display a blank if the cell has an error.

Figure 21

1. Close the brackets and hit Enter

This is how the final formula should look like (in this particular case).

Figure 22

And now the table should look like that:

Figure 23

STEP 6. FILLING OTHER COLUMNS

Since now we have the ultimate formula, we can simply copy it to the other empty columns. However, we still have to make a small change.

We move formula to the other column, which means that we need to change the value of the Col_index_num depending on what column we want to fill.

In this case, we want to fill the SALARY column which is 5^th from the left on the original worksheet, thus we have to change the formula by replacing 4 to the 5. Watch a gif.

All the next columns are filled in the same way. Now it is only needed to format the cells.

Figure 24

In the end, we get 2 combined workbooks on 1 worksheet.

If you want to learn how to combine data from multiple worksheet in excel, we recommend you read this article.

Our Friends at Extend office also has a great article on the same topic of combining two workbook into one