This page shows how to use the Excel Vlookup Function when you want an exact match to the lookup value.
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.
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.
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||-||lookup_value is therefore the text string "Benson, Paul".The|
|'Hourly Pay'!A:B||-||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||-||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.