How to use VLOOKUP in Excel 2016 (example included)

VLOOKUP is a powerful function in Excel. 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 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])

Let’s now explain each element within this function:

  1. lookup_value is the value that is present in your first table, which you wish to find in your second table
  2. table_array is the range of cells for your second table
  3. col_index_num is the column position in your second table. For example, the value of ‘2’ represents the second column in your table
  4. [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’ll review an example to demonstrate how to 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

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-NameClient Last-NameCountry Code
EldenStanhope107
EmileNation103
VeldaLabriola105
VanesaRakowski109
SharronGorby120
CeciliaSwearingen101
DorethaSaffell106
MariahHeilman103
RanaBigby107
InVallecillo116
LibradaTaylor119
ElaneMayes103
AlixHaskett109
AnastaciaGoldblatt115
MelaniaWhitlock108
Dario Parker111
RochelWitherspoon106
LucreciaTarrance114
JermaineLamprecht102
RegineBascom109
BryannaStansberry107
NaomiArai114
DarinBlumstein108
GregRicketts102
WilburnGuse118

 

This is how the client data would look like in Excel:

 

Data in MS 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 CodeCountry Name
101Australia
102Austria
103Belgium
104Belize
105Canada
106China
107Finland
108France
109Germany
110Greece
111Italy
112Japan
113Mexico
114Morocco
115Nigeria
116Peru
117Russia
118Sweden
119United Kingdom
120Uruguay

 

And this is how the country data would look like in Excel:

 

Data-set in MS 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 connect 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

Let’s now look at 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:

=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 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:

 

How to use VLOOKUP in Excel 2016

 

(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”:

 

VLOOKUP in Excel 2016

 

(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:

 

VLOOKUP function in Excel

 

The VLOOKUP is now fully applied. Mission accomplished!

You may want to check the following link for additional examples and tutorials on Excel 2016.