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.
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.
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.
If the leftmost columns contain duplicates, then VLOOKUP behave differently. In case of an Exact match, VLOOKUP returns the first occurrence of a match.
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
Download the Sample Excel File for practice.
|vLookUp.xlsx||Login to Download|
Please login to post your valuable comments.