Quick, clean, and to the point

This video comes from our Excel video training library.

How to use VLOOKUP to merge tables

VLOOKUP is a great way to merge data in two tables based on a common ID. This video shows you how in 3 minutes. You can even use this technique to reorder the columns you retrieve.
Video Transcript 

In this video, I'll demonstrate how you can use VLOOKUP to join data in separate tables.

In this worksheet, we have two tables. In the first table, we have order data. You can see we've got a date, customer id, product, and total.

In a second sheet, we have customer data. We've got first and last name, street, city, state and so on. And, because the id is in the first column, we can use VLOOKUP to extract all the data in this table to the right of the id.

To start off, I'm going to do two things to make our VLOOKUP formulas a little easier to enter and read. First, I'll create a named range for the customer data. I'll use this name inside VLOOKUP in a just moment. Second, back in the order data, I'll number the empty columns, starting with 2 in column F. Now, notice that the order of these columns is exactly the same in both sheets. You'll see how this works in just a minute.

Now let's enter the first VLOOKUP formula to pull in last name.

The lookup value comes from column C. And I need to lock the column, so that it won't change as we copy formulas across the table.

For table array, I need to use the named range I just created, "customer_data". Named ranges are absolute by default, which will work perfectly in this situation.

The column number comes from row 3 above. Here, I need to lock the row number, so that it won't change as we copy down the table.

Finally, I need to set VLOOKUP to exact match, by using zero or false for range_lookup.

When I copy this formula across the table, VLOOKUP pulls customer data into each column.

Then I can simply double click to fill in the rest of the table.

And there we have it. We've joined customer data with order data.

Now, the column numbers above could be calculated with a more complicated formula. But one nice benefit to keeping the numbers exposed on the worksheet, is that I can easily reorder the columns if I want to. For example, I can reorder last and first names simply by swapping the column numbers.

Finally, if you don't need the formulas you've joined data like this, you can get rid of them using Paste Special.

Select the VLOOKUP formulas, copy to the clipboard, then use Paste Special with Values.

Dave Bruns