ExcelFunctions.net

Search Site:

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.

Hourly Pay: |
Sales Team Hours: |

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: |

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: |

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.