VLOOKUP Function

MS Excel is used by businesses and researchers to store huge volumes of data which run into thousands of rows and columns. In such scenarios it becomes virtually impossible to manually find or search some specific values. To solve this problem, MS Excel provides us with LOOKUP Functions.  The major functions under this category are:
1. VLookup
2. Hlookup
3. Index and Match

In this article, we will discuss VLOOKUP Function with help of two examples.

VLOOKUP looks at a value in one column, and finds its corresponding value on the same row in another column. Using VLOOKUP is similar to looking up a person’s name in a telephone book to get a telephone number.

Syntax: Vlookup(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value is the value to be found in the first column of the table and can be a value, a reference or a string.
• table_array is a table of text, numbers, or logical values,in which data is retrieved. Table array can be a reference or a range name.
• Col_index_num is the column number in table_array from which the matching value should be returned. The first column of values in the table is column 1.
• range_lookup is a logical value to find the closest match in the first column( The value can be False or 0 for Exact match and True or 1 for approximate match).

 

Example 1: You have to find out Salary based on the Name entered. The Master Data is from F14:K23.

Step 1: In cell I6 Enter the name of the person whose salary has to be found.

Step 2: In Cell I7 Insert the function VLOOKUP

Step 3: In the given pop up dialog box enter the respective values for each of the arguments:

Lookup_Value: Is cell I6 based on which Salary has to be found out.

Table_Array: $F$14:$K$23, Reference of source data from where the Salary has to be found out.

Col_index_no: 3,is the position of Salary Column starting from left most column in Source Data.

Range_lookup: 0, as we are finding an exact match.

 

 

Example 2: You have to find out tax based on Salary entered. The Master Data is from D1:F7, In this we have to find out an approximate tax value based on Income.

Step 1: In cell B2 , Enter the value for income.

Step 2: In Cell B3, Insert the Function Vlookup

 

Step 3: In the given pop up dialog box enter the respective values for each of the arguments:

  

Lookup_Value: Is cell B2 based on which tax has to be found out.

Table_Array: $D$1:$F$7, Reference of source data from where the Salary has to be found out.

Col_index_no: 3,is the position of Salary Column starting from left most column in Source Data.

Range_lookup: 1, as we are finding from a range not an exact match.

 

 

Fill in your details to get our latest handbook with 200+ Fourmulas and Shortcuts

 

Also check out other articles on similar topics:

HLOOKUP Function

INDEX MATCH Function