VLOOKUP is one of the most important formula/ function in excel. In this post I will explain how to use VLOOKUP function in excel in detail and also the limitations of it. The more you use it, the simple it becomes. Before getting into the post let’s understand and break few Myths about VLOOKUP Function.
VLOOKUP is difficult to Learn and Use – NO
Only Advanced Excel Users use VLOOKUP – NO
If a person know how to use VLOOKUP he/ she is a Master in Excel – NO
It is common question in excel interviews, Do you know how to use/ apply VLOOKUP – YES
Let’s get started…
What is VLOOKUP Function in Excel
VLookup is a reference function and is short name for “Vertical Lookup”.
VLookup searches for a Specific value in the left most column data and returns a corresponding value from the row that is chosen. In simple words vlookup retrieves data from a specific column in a bigger data set. This function is available in all the versions of Excel from Excel 2000 to Office 365.
How to write VLOOKUP Function in Excel
Syntax
The syntax for VLOOKUP Function in excel is as follows
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Let us breakdown the syntax.
Arguments of Vlookup function in Excel
The arguments for this function are lookup_value, table_array, col_index_num and range_lookup (optional).
Lookup_Value : The Value to look/search for. This can be a Text/ Number/ Date/ Value or a Cell Reference. Output or return value from another function can also be used as lookup_value.
Table_Array : Data set from which the lookup_value needs to be matched. This can be a single column/ more than one column or a Table or a Named Range
Suggested Reading
- Know about how to create and use Named Ranges in Excel
- Know about how to create and use Tables in Excel
Col_Index_Num : Column Number in the table_array from which the value in corresponding row should be retrieved.
Range_lookup : To determine an Exact Match or Approximate Match. Choose False or 0 for an Exact Match and True or 1 for an Appropriate Match. This is an optional parameter but this is most important to determine the output of the Vlookup function. If we leave this blank Vlookup takes Approximate Match by default.
In cases where we choose Approximate Match (TRUE or 1), Vlookup returns Exact Match if available. In cases where exact match is not available Vlookup returns the next largest value that is less than the lookup value.
Note:
- If lookup value is a number and is less than the smallest number in the first column of the table_array it returns a #N/A error.
- If Column Index Number is less than 1 or is greater than the total number of columns in your Table_Array then also the function returns a #Value and #Ref errors respectively.
- In cases where you select the Match as Approximate (TRUE or 1) ensure that the data in Ascending Order or sort the data in ascending order.
Examples
See the below examples to understand VLookup function.
In the below example I have the sales details of two quarters for different products (product 1 to Product 20). Now I want sales details of products Product 20 and Product 17. Let’s see how to get it using Vlookup. See below screen shot.
The above screenshot show how to do Vlookup using a Cell Reference. Instead to do a Vlookup
Suggested Reading: Know more about how to use cell references in excel
- With Text (In this case product Id) – =Vlookup(“Text”,Range,Col Num,False) [Ensure that you use “ “ while using Text criteria]
- With a number – =Vlookup(Number,Range,Col Num,False)
Points to Remember
Vlookup is Case Insensitive. Meaning, it treats both uppercase and lower case in the same way. As an example Excel treats “APPLES” and “apples” as one and the same.
It is a Left Function. Means it Searches for look up value in the 1st column of the data range.
Vlookup returns the 1st Match found. This is one of the biggest limitation of Vlookup. If your lookup value is repeating twice in 1st column of data Vlookup always returns the data related to 1st Match. For example if your look up value is “Paper” which is there in 5th row and 10th row in first column of data, the details in 5th row are retrieved.
Do you use VLOOKUP function?
Please do share the best practices or any issues while using VLOOKUP function in the comment box below.
Do share this post with your friends if you find it useful.
Pingback: INDEX Function in Excel - How to use with Examples - Excel Bits
Pingback: How to use MATCH Function in Excel - Syntax and Examples - Excel Bits