How to use VLOOKUP in Excel 2010

VLOOKUP is a powerful function in Excel that can save you a lot of time in managing your data… In this post, I’ll show you how to use VLOOKUP in Excel 2010.

Why use the VLOOKUP function in Excel?

VLOOKUP can help you connect/associate data that exists in two (or more) separate tables.

To illustrate, suppose you are the owner of a distribution company. You maintain your client data in one table, and the country of distribution data in a second table.

In order to link/connect those two separate tables together, you can use the VLOOKUP function in Excel.

Please note that in order to link those two separate tables, you’ll need a common value that appears in both tables.

In our example, I included a common column called ‘Country Code’ under both tables. The values under this common column will be used as the linkage point between the two tables.

Preparing the Data-sets

But before you can apply the VLOOKUP function, you’ll need to copy the following two data-sets into Excel:

– Client Data

– Country Data

In order to accomplish this task, you’ll need to perform the steps below:

(1) First,  copy the following client data into an Excel sheet (i.e., copy the client data into Sheet1):

 

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

 

(2) Next, rename the Excel sheet (i.e., Sheet1) to ‘Client Data’ 

To rename the Excel sheet, double-click on the tab ‘Sheet1’ at the bottom of the sheet, and then type ‘Client Data’

Your first Excel sheet should look as follows:

 

Data in Excel

 

(3) Next, you’ll need to copy the following Country Data into a second Excel Sheet (i.e., copy the country data into Sheet2):

 

Country CodeCountry Name
101Australia
102Austria
103Belgium
104Belize
105Canada
106China
107Finland
108France
109Germany
110Greece
111Italy
112Japan
113Mexico
114Morocco
115Nigeria
116Peru
117Russia
118Sweden
119United Kingdom
120Uruguay

 

(4) Now, rename the Excel sheet (i.e., Sheet2) to ‘Country Data’ 

As before, you can rename the Excel sheet by double-clicking on the tab ‘Sheet2’, and then typing ‘Country Data’

Your second Excel sheet should look as follows:

 

Table in MS Excel

The VLOOKUP Structure

The VLOOKUP function in Excel follows the structure of:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

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

Steps to apply VLOOKUP in Excel 2010

Now you are ready to apply the VLOOKUP function in Excel:

(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 table (i.e., the Client Data table), which you wish to find in your second table (i.e., the Country Data table)

– ‘Country Data’!A:B is your table_array. It represents the range of cells that contain your second table (i.e., the Country Data table) where you want to find your lookup_value

Here, ‘Country Data’! represents the second sheet where your second table is located. While A:B represents the columns that contain your second table

– 2 is your col_index_num. It represents the column position in your second table (i.e., the Country Data table). 

Here, ‘2’ means the second column under your second table. The values under this second column will then be displayed when you apply the VLOOKUP function 

– TRUE is your [range_lookup]. It represents an approximate match

This is how your VLOOKUP function will look in Excel:

 

How to use VLOOKUP in Excel 2010

 

(4) Press Enter. You’ll now notice that the result of your VLOOKUP function will be populated in Cell D2.

The country associated with the country code 107 is Finland.

The VLOOKUP function helped you to automatically associate the client name (from the first table that contained the Client Data) with the country name (from the second table that contained the Country Data).

 

Table in Excel

Apply the VLOOKUP function across your entire data-set

In order to apply the VLOOKUP function across your entire Client data table, go to cell D2, and press the ‘little square’ at the bottom right corner of the cell.

Keep pressing on that ‘little square’

 

Drag cell in Excel

 

Then, drag the little square from cell D2 all the way to cell D26 to cover your entire client data-set:

 

Drag formula across a column in Excel 2010

 

You’ll now notice that all the country names, associated with the country codes, are now displayed for each client record:

 

How to use VLOOKUP in Excel 2010

 

Congratulation, you have now applied the VLOOKUP function across your data-sets…