How to use VLOOKUP to merge tables

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 that 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 of 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 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 the formula down the table.

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

When I copy the 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 these 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 after you've joined data like this, you can get rid of them using Paste Special. Just select the VLOOKUP formulas, copy to the clipboard, then use Paste Special with Values.