This Vlookup tutorial begins by addressing the common question "What is Vlookup in Excel?"
Vlookup is one of Excel's built-in functions. The function is used when you want to find a value in the left-hand column of a vertical array of data, and return the corresponding value from another column in the same array.
This best explained by way of an example.
Imagine that your company keeps a spreadsheet of employee hourly rates of pay (see example on left below). At the end of each month, the sales team manager sends you a list of hours worked by each of his staff during the month (see example on the right below). It is your job to complete the spreadsheet on the right, to show the pay owed to each member of the sales team.
Hourly Pay: | Sales Team Hours: |
In order to calculate the pay owed to each sales team member, you first need to look up the hourly rate of pay for each person and insert this into column C of the monthly spreadsheet. This can be done using the Excel Vlookup function.
As illustrated below, if entered into cell C2 of the 'Sales Team Hours' spreadsheet, the Vlookup function can be used to look up the name "Benson, Paul" in the 'Hourly Pay' spreadsheet and return the corresponding rate of pay from column B. Therefore, in this example, the Vlookup function returns the value $32.00.
The full syntax of the Vlookup formula in the above example is not shown as, at the moment, we simply wish to explain what the Vlookup function does. The syntax of the function is discussed in detail in a later part this tutorial.
However, for those who want to skip ahead, the above Vlookup is shown in part 3 of this tutorial: Vlookup Example of Finding an Exact Match.
In the above example the Vlookup is used to find an exact match to the lookup value (which is the text string "Benson, Paul" in this case).
The Vlookup function can also be used to return a closest match to the lookup value if an exact match is not found. An example of this is given in part 4 of this tutorial: Vlookup Example of Finding the Closest Match.
A more in-depth description of the Vlookup function is provided in a short course, which is provided on the Microsoft Office website.