Excel Vlookup Tutorial Part 3 - Vlookup Example With Exact Match
This page shows how to use the Excel Vlookup Function when you want an exact match to the lookup value.
Vlookup Exact Match Example
In this example, the Excel Vlookup function is used to 'look up' the amount of pay owed to each member of a sales team during the previous week.
As shown below, the employee hourly rates of pay are stored in one spreadsheet, and a list of hours worked during the previous week, are stored in a second spreadsheet.
|
Hourly Pay: 
|
Sales Team Hours Worked: 
|
In order to complete the "Sales Team Hours" spreadsheet, so that it shows the pay owed to each sales team member, you first need to 'look up' each persons rate of pay from the "Hourly Pay" spreadsheet.
In the spreadsheet below, cells C2-C4 of the "Sales Team Hours" spreadsheet have been updated with the Vlookup function, which looks up the hourly pay rates for each of the sales team members.
|
Sales Team Hours Worked with Vlookup Functions: 
|
The results of these Vlookup functions are shown in the spreadsheet below. As expected, each team member's hourly rate of pay has been inserted into the corresponding cell in column C.
|
Sales Team Hours Worked with Vlookup Function Results: 
|
Vlookup Example Explained
The first call to the Vlookup function, in C2 is:
=VLOOKUP( A2, 'Hourly Pay'!A:B, 2, FALSE )
The table below explains the arguments that are supplied to this function:
| A2 | - |
Cell A2 is the lookup_value (i.e. the value to be searched for). The lookup_value is therefore the text string "Benson, Paul". |
| 'Hourly Pay'!A:B | - |
The table_array is made up of columns A-B of the "Hourly Pay" spreadsheet. The Vlookup function will search down the leftmost column (i.e. column A) of this array, when searching for the text string "Benson, Paul". |
| 2 | - | The col_index_num argument is set to the value 2, which tells the Vlookup function that the value to be returned should be taken from column 2 of the supplied table_array (i.e. from column B of the "Hourly Pay" spreadsheet). |
| FALSE | - |
The [range_lookup] argument is set to FALSE, which tells the Vlookup function that we require an exact match to the lookup_value. If an exact match is not found, then the Vlookup function should return an error. |
For further exact match Vlookup examples, see the Microsoft Office website.