How to use VLOOKUP to merge tables

In this worksheet we have two tables.

In the first table, we have order data.

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

First, I’llcreate a named rangefor the customer data.

I’ll use this name inside VLOOKUP in just moment.

You’ll see how this works in just a minute.

Now let’s enter the firstVLOOKUP formulato pull in “last name.”

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.

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.

For example, I can reorder last and first names simply by swapping the column numbers.

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