Microsoft Excel Tutorial Tutorial

vLookUp - How to use vLookUp function in Microsoft Excel

VLOOKUP

In this article, we will learn How to use VLOOKUP function in Microsoft Excel? At the time of writing this article, we used the latest available version of Excel. If you are looking for the tutorial for any specific version, feel free to change the version of this article.

What is VLOOKUP?
VLOOKUP is a Microsoft Excel built-in function to search a table using its first column. On the successful match, we can get the value of the required column as an output. In VLOOKUP letter "V" stands for vertical. It means we can use VLOOKUP for vertical search operations.

Syntax
VLOOKUP is a function required 4 arguments. Following is the syntax for VLOOKUP function.
=VLOOKUP (1. Search Input, 2. Search Range, 3. Output Column, 4. Match Condition [Optional])
1. Search Input: First argument is the search string. It is the string you want to search within the range.
2. Search Range: The second argument is the range where you want to perform the search. Always remember VLOOKUP use the first column to perform a match with the input search string. It means VLOOKUP always provide the value from one of the right columns within the selected range.
3. Output Column: The value of the column, we need to display on a successful match. We need to provide the cell index. Cell index must be a positive value and it must be greater than 0.
4. Match Condition [Optional]: It is a boolean value. If we are looking for an exact match, then provide False. True for an approximate match.

How to Use?
The following image demonstrates How to Use VLOOKUP. In the example on the left, we have a contact table with three columns ID, Name, and Email. On the right, we have a search box. Based on the provided Contact ID VLOOKUP returns the contact email. All the data in this example is only for the training purpose. Only the email for Mayank Sanghvi and vLemonn Support team are valid.

Match Condition 

The fourth argument in the VLOOKUP function is optional. It is used to define the Match Criteria. VLOOKUP support two different match conditions. First is the approximate match, and the second is the exact match. Switch to sheet number 2 "Search Condition". In this sheet, on the left, we have eight point grading system introduced by the University of India. At right, we have a setup to find out the student grade based on the marks. Check the VLOOKUP function we used this time. 
=VLOOKUP(G8,B7:C15,2) 
We only used VLOOKUP with three arguments. The fourth argument or the Match condition is optional. By default, VLOOKUP performs an approximate search. 
 
What is Approximate?
 
Check the Student, Marks. We provide 82.00. It is not available on the left side grading table. In case of approximate search, if the VLOOKUP function not able to find the exact value in the first column, then it will search for the largest value but smaller to the input value. In the example, we used 82. In the range, we have two values close to 82. Which is 80 and 90. But 90 is greater than 82 i.e. VLOOKUP will select the grade for the row with value 80.
 
Refer the following image for more details.

Exact Match?

Our first example of Search contact is an example of an Exact match. In the search contact, we are looking for an exact match of Contact ID. In case of an exact match, we need to provide either false or 0 in the fourth argument. This is one of the common mistakes done by most of the Excel users. So in case, you are not getting the expected output change the value of the fourth argument.

Duplicate Values

If the leftmost columns contain duplicates, then VLOOKUP behave differently. In case of an Exact match, VLOOKUP returns the first occurrence of a match.

Case Sensitive

VLOOKUP function is not case sensitive. In the below image Excel, EXCEL, excel, EXCEL, eXCel, ..., all return the same first matching row.

Join or Combine Data

We can use the VLOOKUP function to combine data between multiple tables. We split our Contact table into two separate tables. In the first table, we have the First Name for all the contacts. In the second table, we have the last name of the contacts. Our goal is to use the VLOOKUP function to generate the Full Name for Contacts.
Following is the final expression to find out the full name.

Download the Sample Excel File for practice.

FileDownload
vLookUp.xlsxLogin to Download
...

About The Author

Hello, I am Mayank Sanghvi I am primarily a BI developer with experience in MSBI and Cognos BI Suit. Also having interest in various other technologies such as Android, Java, C#, ASP .NET and other. I enjoy learning new technologies and share my experience with others.

0 Comments
Leave A Comment

Please login to post your valuable comments.

add