How to use VLOOKUP Function in Excel

Last modified date

How To use VLOOKUP In Excel

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.

How To use VLOOKUP In Excel
VERTICAL & HORIZONTAL

Sample of VLOOKUP Function

How To use VLOOKUP In Excel
Sample Of EXCEL VLOOKUP

The Syntax of VLOOKUP Function

VLOOKUP:(lookup_value,table_array,col_index_num,[range_lookup])

  • 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 lookup the value.
  • 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.
  1. (You can also use “0” Zero as FALSE means Exact Match or 1 as TRUE Approximate Match).
  2. Though it is an optional argument. It is advisable to use this argument.
  3. It is advisable to select False for Exact match.
  4. 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

How To use VLOOKUP In Excel
Excel VLOOKUP Function Example 1

Example 1 Using VLOOKUP Get the Student Name from Source Data. Common point is Hall ticket number. (Common point = Lookup_Value)

Formula in cell D6 is =VLOOKUP(C6,$C$13:$D$18,2,FALSE)

Result is Student1

How To use VLOOKUP In Excel
Excel VLOOKUP Function Example 2

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

How To use VLOOKUP In Excel
Excel VLOOKUP Function Example 3

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 VLOOKUP Function in EXCEL .

Practice questions VLOOKUP Function

Click here to download the Excel Vlookup Sample questions workbook for practice

Video related to Excel VLOOKUP Tutorial in my YouTube Channel. Please Subscribe to my Channel and Hit the Bell Icon for regular Notifications.

Excel VLOOKUP Video Tutorial

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment