This page shows how to use the Excel Vlookup Function when you want an exact match to your lookup value.
In the following example, the Excel Vlookup function is used to 'look up' the amount of pay owed to each member of a sales team during the last month.
As shown below, the employee hourly rates of pay are stored in one spreadsheet, and a list of hours worked during the last month, are stored in a second spreadsheet.
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.
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 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:
The first call to the Vlookup function, in C2 is:
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 table_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 table_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 Vlookup function should return an error.
Further Vlookup examples are provided on the Microsoft Office website.