VLOOKUP is a powerful function in Excel. In this tutorial, I’ll show you how to use VLOOKUP in Excel 2016.
Specifically, I’ll review an example with the steps needed to apply a VLOOKUP. But before we begin, let’s first review the elements of the VLOOKUP function.
Elements of the VLOOKUP in Excel 2016
The VLOOKUP function in Excel has the following structure:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value is the value that is present in your first table, which you wish to find in your second table
- table_arrayis the range of cells for your second table
- col_index_num is the column position in your second table. For example, the value of ‘2’ represents the second column in your table
- [range_lookup] can assume either a ‘TRUE’ value for an approximate match, or a ‘FALSE’ value for an exact match
Example of a VLOOKUP Function
To start, let’s review an example, where you have two tables in Excel:
- The first table contains Client Data with 3 columns: Client First Name, Client Last Name and Country Code. Let’s call this table the ‘Client’ table
- The second table contains Country Data with 2 columns: Country Code and Country Name. Let’s call this table the ‘Country’ table
For each client name, under the first Client table, the goal is to display the Country Name (to be taken from the second Country table). You can then use the VLOOKUP function to automate this task.
Here is the Client data that can be copied into Excel (under the range of cells A1 to C26). You’ll need to rename the first sheet to Client Data:
|Client First-Name||Client Last-Name||Country Code|
This is how the client data would look like in Excel:
Next, copy the Country data into a second sheet under the range of cells A1 to B21. You’ll need to rename the second sheet to Country Data:
|Country Code||Country Name|
And this is how the country data would look like in Excel:
Notice that both of the tables have the column Country Code, which contains common values under both tables. We will use the values under the Country Code column to connect between the two tables.
Once you connected the two tables via the Country Code, you’ll be able to achieve the goal of displaying the Country Name for each client under the Client table.
Steps to Apply the VLOOKUP in Excel 2016
Here are the steps to apply the VLOOKUP function:
(1) First, go to the ‘Client Data’ sheet
(2) Next, double-click on cell D2
(3) Then, type/copy:
- C2 is your lookup_value.It represents the value that is present in your first Client table, which you wish to find in your second Country table
- ‘Country Data’!A:B is your table_array. It represents the range of cells for your second Country table, where you want to find your lookup_value
- The value 2 is your col_index_num. It reflects the column position in your Country table. Here, ‘2’ means the second column under your Country table. The values under this second column (i.e., the Country Name column) will then be displayed when you apply the VLOOKUP
- TRUE is your [range_lookup]which reflects an approximate match
This is how your VLOOKUP function would look like in Excel:
(4) Press Enter, and you’ll now see the result of your VLOOKUP function in cell D2. In our case, the country name associated with the country code of 107 is “Finland”:
(5) Finally, drag-down the VLOOKUP function from cell D2 to cell D26, so that you can display the Country Name for each client under your first Client table:
The VLOOKUP is now fully applied. Mission accomplished!
You may want to check the following link for additional examples and tutorials on Excel 2016.