VLOOKUP is one of the mostly heard formula/ function in excel. In this post I will explain how to use VLOOKUP 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
VLOOKUP is only used by Advanced Excel Users – 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…
The VLOOKUP Function – Why and what it do
VLookup is a reference function and is short for “Vertical Lookup”.
It searches for a Specified value in the left most column data and returns a corresponding value from the row that’s 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 Excel 2016.
How to write VLOOKUP Function
The syntax for VLOOKUP Function is as follows
Let us breakdown the syntax.
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 of/ value returned by another function can also be used as lookup_value.
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 nothing is chosen VLookup takes Approximate Match by default.
In cases where Approximate Match (TRUE or 1) is used, 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.
- 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 the Match is set as Approximate (TRUE or 1) ensure that the data is sorted in Ascending Order.
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
- 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. For Example “APPLES” and “apples” are considered 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.