Back to: Spreadsheets – Excel
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.
First, insert two tables.
1. Select a cell in the first range.
2. On the Insert tab, in the Tables group, click Table.
3. Excel automatically selects the data for you. Click OK.
4. Repeat these steps for the second range.
Result so far:
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.
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.
10. Press Enter.
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!
Conclusion: tables can make VLOOKUP formulas a lot easier. Try it yourself. Download the Excel file and enter a new city and country.