VLOOKUP in excel is the popular and widely used formula in MS Excel. VLOOKUP function searches given value ( lookup_value) in the left most column of the table and returns the value in the given column (col_index_num) of the matching row.
The syntax for VLOOKUP in excel:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Lookup_value: lookup_value is the value you want to search in a table or array.This can be text, number or logical vaues and are not case sensetive.
table_array: this is the range of cells that is to be searched for the lookup value. The formula searches for the lookup value in the first column of the table.
col_index_num : this is the column number in the table_array from which the matching value for the lookup_value is to be returned. This column number should be with in the range of table_array selected. The formula brings the value in the given column for matching value.
range_lookup: this is the optional argument. This will define whether the vlookup should return exact match or approximate match. If lookup range is TRUE then the formula will search for exact match, if exact match not available it will return the approximate match. If only exact match required then FALSE is to be entered. Instead of TRUE or FALSE 1 or 0 can also be entered correspondingly.
For Example in one file you have employee master data like Employee No., Name, Bank Account No. and in another file employee salary details with employee No. for a particular month.
From the available information in the two files, if you want to prepare a salary statement with bank account number which is to be sent to bank for payment, you can use the VLOOKUP function and combine the information in two files based on the employee Number.
|click to enlarge the picture|
In the given example, to update the salary details in Payment Statement the formula will search the employee number in Pay roll data table and return the value in 6th column for matching employee number. If there are no records matching for the employee number, it will show the error.