VLOOKUP is a powerful function in Excel that can save you a lot of time in managing your data! In this tutorial, I’m going to show you how to use VLOOKUP in Excel 2016.
Specifically, I’m going to review an example with the steps needed to apply a VLOOKUP.
But before we begin, let’s first view the elements of the VLOOKUP function. This will then help us understand how to apply this function across multiple scenarios.
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])
Let’s break down each element within this function. I’ll use color-coding to explain each element:
- lookup_value is the value that is present in your first table, which you wish to find in your second table
- table_array is the range of cells that contain your second table, where you want to find your lookup_value
- col_index_num is the column position in your second table. For example, the value of ‘2’ represents the second column in your table. The values under this column will be displayed when you apply the VLOOKUP (i.e., once your lookup_value is found in the second table)
- [range_lookup] can assume either a ‘TRUE’ value for an approximate match, or a ‘FALSE’ value for an exact match
In the next section, we will review an example to illustrate how you can use VLOOKUP in Excel 2016.
Example of using the 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
Our goal is to display the Country Name (to be taken from the second Country table) for each client name under the first Client table.
Using VLOOKUP, you can connect the data between those two tables.
We will use the column ‘Country Code’ to connect the two tables, as this column contains common values that appear under both tables…
Here is the Client data that I copied into Excel (under the range of cells A1 to C26). I then renamed the sheet as Client Data:
|Client First-Name||Client Last-Name||Country Code|
This is how the client data would look like in Excel:
Next, I copied the country data into a second sheet (named as Country Data) under the range of cells A1 to B21:
|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. As indicated earlier, we will use the values under the Country Code column to connect between the two tables.
Once we connect the two tables via the Country Code, we will be able to achieve our goal of displaying the Country Name for each client under the Client table.
Steps to apply VLOOKUP in Excel 2016
Let’s look at the steps to apply the VLOOKUP function in more detail:
(1) First, go to the ‘Client Data’ sheet
(2) Next, double-click on the cell D2
(3) Then, type: “=VLOOKUP(C2,‘Country Data’!A:B,2,TRUE)”
- 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 that contain 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 second 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 results 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.