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-Name||Client Last-Name||Country Code|
(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:
(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 Code||Country Name|
(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:
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:
(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).
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’
Then, drag the little square from cell D2 all the way to cell D26 to cover your entire client data-set:
You’ll now notice that all the country names, associated with the country codes, are now displayed for each client record:
Congratulation, you have now applied the VLOOKUP function across your data-sets…