Click Here to download the Excel File which contains the Below Examples.
Description of VLOOKUP Function
VLOOKUP: VLOOKUP means vertical lookup. This Function will help to get value from source place to destination place using the common data point between 2 places (Or) 2 different tables.
Sample of VLOOKUP Function
The Syntax of VLOOKUP Function
- 1st Argument (Compulsory): lookup_value
- 2nd Argument (Compulsory): table_array
- 3rd Argument (Compulsory): col_index_num
- 4th Argument (Optional): [range_lookup]
(For any Excel Function Syntax if we have square brackets then it is optional)
Arguments description of VLOOKUP Function
- lookup_value 1st Argument compulsory lookup_value means common point between source and destination cells. Here we need to select the cell for the value you want to lookup.
- table_array 2nd Argument compulsory table_array means range which should be covered our common point and required result. Here we need to select the range where you want to
- col_index_num 3rd Argument compulsory col_index_num means to count the position of the required column number from our common point column number in the source data.
- [range_lookup] 4th argument optional [range_lookup] means how our common point should match in source data, the First option is Exact Match or the Second option is Approximate Match.
- (You can also use “0” Zero as FALSE means Exact Match or 1 as TRUE Approximate Match).
- Though it is an optional argument. It is advisable to use this argument.
- It is advisable to select False for Exact match.
- If we are completely aware of what result will get if we use TRUE or else if there is a specific requirement to use, then only Select the option TRUE.
Note: If you do not select TRUE or FALSE. By default, value is TRUE.
Explanation of VLOOKUP Function with Examples
Example 1 Using VLOOKUP Get the Student Name from Source Data.
Formula in cell D6 is =VLOOKUP(C6,$C$13:$D$18,2,FALSE)
Result is Student1
Example 2 Using VLOOKUP get Science marks from Source Data. The common point is the Hall ticket number (Common point = Lookup_Value)
Formula in cell D35 is =VLOOKUP(C35,$D$43:$F$48,3,FALSE)
Result is 26
Disadvantage of VLOOKUP Function
Example 3 Using VLOOKUP get Science marks from Source Data. The common point is the Student Name.
(Common point = Lookup_Value)
Formula in cell D60 is =VLOOKUP(C60,$C$67:$F$70,4,FALSE)
Result is INCORRECT
Note: – Whenever we have duplicate Lookup value in our source data then, we will get the result for the First match only. That is the in above example we see the result as INCORRECT but we are expecting 26. LOOKUP Value duplication in source data should be cross-checked before applying
Practice questions VLOOKUP Function
Click here to download the Excel Vlookup Sample questions workbook for practice
Video related to Excel VLOOKUP Tutorial