Merge Tables

 

You can use tables and the VLOOKUP function in Excel to quickly merge two tables. This trick will bring your Excel game to a new level.

Our goal is to create one table that consists of a list of cities, countries, and country codes.

Two Ranges in Excel

First, insert two tables.

1. Select a cell in the first range.

2. On the Insert tab, in the Tables group, click Table.

Insert Table

3. Excel automatically selects the data for you. Click OK.

Create Table

4. Repeat these steps for the second range.

Result so far:

Two Tables

Now comes the most important part.

5. Select cell C2 and enter =VLOOKUP(

6. Enter the first argument by selecting cell B2. Excel replaces this cell reference with [@Country] because we're working with tables here. This is called a structured reference.

7. Enter the second argument by selecting the range E2:F6. Excel replaces this range reference with Table2.

Structured References

8. Use 2 for the third argument because the codes are in the second column of the red table.

9. Use FALSE for the fourth argument to find an exact match.

VLOOKUP function

10. Press Enter.

Merge Tables in Excel

Note: Excel automatically copied the VLOOKUP function to the other cells. That saves time.

11. Enter a new city and country. Excel automatically looks up the corresponding country code for you. Wow!

Table Magic

Conclusion: tables can make VLOOKUP formulas a lot easier. Try it yourself. Download the Excel file and enter a new city and country.