# INTRODUCTION

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:

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

Figure 2

Figure 3

- Click on the “Move or Copy Sheet…” option. The dialog box will appear.
- Put a check in the “Create a copy” box.
- Open the drop list to choose the book where you want to copy the worksheet. In this case, it is Workbook 1.
- 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:

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

**OR**

- 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

**Lookup_value**– the value that you are looking for**Table_array**– the range where you are looking for the wanted value**Col_index_num**– the column number in the lookup range containing the return value**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:

- Got to the
**worksheet**where you will seek for values - Select the
**range**where you will seek for values (do not include headers)

Figure 12

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

Figure 13

- 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:

**Value**– argument that will be checked for an error.**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:

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

Figure 20

- 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

- 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.

Figure 25

Notes for Posting

- GIF pictures are in a separate folder with the same name
- Start Files available to download.