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?
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.
- 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.
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
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.
STEP 4. TIME TO USE THE VLOOKUP
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
- Going to the Formulas tab Lookup & Reference VLOOKUP
Both ways will bring you to the Function Arguments dialog box
VLOOKUP function has 4 arguments:
- 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.
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)
- Go to the Formulas tab and click the Define Name button. The dialog box will open.
- In the dialog box insert a name for the range. You can use any name you want to. In this case, we use “Table2”.
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 4th from the left. Thus, we put 4 in the Col_index_num field.
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.
STEP 5. FACING PROBLEMS
However, sometimes when the original list is not full. You can get this result:
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.
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:
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.
- 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.
- Close the brackets and hit Enter
This is how the final formula should look like (in this particular case).
And now the table should look like that:
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 5th 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.
In the end, we get 2 combined workbooks on 1 worksheet.
Notes for Posting
- GIF pictures are in a separate folder with the same name
- Start Files available to download.