How to use VLOOKUP in Excel 2016

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:

  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 that contain your second table, where you want to find your lookup_value
  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. 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)
  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 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-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, I copied the country data into a second sheet (named as Country Data) under the range of cells A1 to B21:

 

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

 

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.