Vlookup (short for ‘vertical’ lookup) is a built-in Excel function that is designed to work with data that is planned into columns. For a described value, the function discoveries (or ‘looks up’) the value in one column of data, and returns the consistent value from another column.
Vlookup Syntax & Rules
This page delivers details of the Vlookup function syntax and rules of use.
Vlookup Syntax
The syntax of the Excel Vlookup function is:
VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
Where the function arguments are:
| lookup_value | The value that you need to search for. |
| table_array | The collection of data that is to be finding for the lookup_value.
Note: The Vlookup function finds in the left-most column of this array. |
| col_index_num | An integer, specifying the column variety of the provided table_array that you just need to return a value from.
|
| [range_lookup] | An elective logical argument that describes what the function ought to return within the event that it doesn’t search a definite match to the lookup_value.
The [range_lookup] is set to TRUE or FALSE, meaning: TRUE – If a definite match isn’t found, use the nearest match below the lookup_value. Note: If this selection is used, the left-hand column of the table_array should be in ascending order. FALSE – If a precise match to the lookup_value isn’t found, return an error. If the [range_lookup] value is omitted, it uses the default value of TRUE. |
This best explained through the following example.
Vlookup Example
Imagine that your company keeps a workbook of staff hourly rates of pay (see ‘Hourly Pay’ spreadsheet below). At the end of every week, the sales team manager sends you a listing of hours worked by every of his workers throughout the week (see ‘Sales Team Hours’ workbook below). It’s your job to finish the ‘Sales Team Hours’ workbook, to indicate the pay owed to every member of the sales team.
In order to calculate the pay owed to every sales team member, you initially ought to research the hourly rate of pay for everyone and insert this into column C of the ‘Sales Team Hours’ workbook. This will be done using the excel Vlookup function.
As illustrated below, if the Vlookup function is entered into cell C2 of the ‘Sales Team Hours’ workbook, this will research the name “Benson, Paul” in column A of the ‘Hourly Pay’ workbook and come back the corresponding rate of pay from column B. Therefore, during this example, the Vlookup function returns the worth $32.00.
In the below example the Vlookup function is used to search an precise match to the lookup value (which is the text string “Benson, Paul”).
