Excel Vlookup Tutorial Part 3 - Vlookup Example With Exact Match

The Vlookup example on this page was introduced to you in part 1 of the Vlookup Tutorial. In this section, we go through the function syntax, so that you can get a full understanding of how the Vlookup works.

This example works with the two spreadsheets shown below. The spreadsheet on the left shows the employee hourly rates of pay for a company, and the spreadsheet on the right gives a list of hours worked by members of the sales team during the last month.



Vlookup Example

 Hourly Pay:
Spreadsheet of Employee Hourly Pay Rates Used in Excel Vlookup Example
 Sales Team Hours:
Spreadsheet of Sales Team Hours Used in Excel Vlookup Example

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.

The following spreadsheet shows the Vlookup function, in cells C2-C3 of the "Sales Team Hours" spreadsheet, used to look up the hourly pay rates for each of the sales team members.

 Sales Team Hours with Vlookup functions:
Spreadsheet of Sales Team Hours Showing Excel Vlookup Formulas

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 with Vlookup functions:
Spreadsheet of Sales Team Hours Showing Results of Excel Vlookup

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 - Sets the lookup_value (i.e. the value to be searched for) to be the contents of cell A2
The lookup_value is, therefore, the text string "Benson, Paul"
'Hourly Pay'!A:B - Sets the lookup_array to be 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 - Tells the function that the value to be returned should be taken from column 2 of the supplied array (i.e. from column B of the "Hourly Pay" spreadsheet).
FALSE - Tells the Vlookup function that we require an exact match to the lookup_value.
If an exact match is not found, then the function should return an error.

Further Vlookup examples are provided on the Microsoft Office website.

Go To Excel Vlookup Tutorial Part 4 - Vlookup Example with Closest Match

Return to the Excel Vlookup Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net