The VLOOKUP function is one of the most popular functions in Excel. I use this on a daily basis. In fact everyone I know who knows a thing or two about Excel uses the VLOOKUP function regularly.
For those of you who don't know what the Excel VLOOKUP function is and what it does then please go to my tutorial on VLOOKUP. Alternatively you can watch my tutorial below which will explain exactly what it does.
As useful as the VLOOKUP function is it however does have its limitations which this post will explain in more detail.
What are the Limitations of the VLOOKUP Function?
So what are the limitations of the VLOOKUP function? Well I will explain what they are below.
1. VLOOKUP Only Looks to the Right
One of the biggest limitations is that the VLOOKUP function can only look at the columns to the right of the lookup value.
Suppose we have a product table and I want to extract information by the product code. The only information I can extract is the price as this column is to the right of the lookup value column. I cannot extract the description and the make of the product.
This means that in order to extract all the information from the product code the Product Code column must be the first column in the table, i.e. the left most column in the table. You would have to manually insert the lookup column to the beginning of the table. This is not so bad if you have to do this once but what if there are hundreds of tables in your worksheet which you have to change manually? This will be a very time consuming task to do.
2. VLOOKUP Only Finds the First Match
If the lookup value column contains duplicate values then VLOOKUP will only extract the first value.
In this example you can see there is a list of sales by sales people. "Jim" is listed three times in the table. When I do a VLOOKUP formula in cell F3 it only extracts the first sales figure which is £100. It will not extract £65 and £88.
3. The Default Setting for VLOOKUP is an Approximate Match
The fourth argument in the VLOOKUP function is the range_lookup. To get an exact match you either use FALSE or a 0. For an approximate match you use TRUE or a 1. In most cases you will want to do an exact match. The problem is that this fourth argument is an optional argument and if this is omitted then Excel will use the approximate match setting. This will cause incorrect results if you want to do an exact match.
In this example I want to extract the sales person using the sales figure as a lookup value. The fourth argument is omitted so it is doing an approximate match. In an approximate match, VLOOKUP assumes the table is sorted. If VLOOKUP finds a value greater than the lookup value then it will return a value from the previous row. The correct answer should be Sarah but it has given the answer Jim which is incorrect.
It is therefore important that if you want to do an exact match you must remember to make the fourth argument either FALSE or 1.
4. VLOOKUP is not Case Sensitive
VLOOKUP does not distinguish between lower and uppercase values. It sees them as the same.
In this example I want to extract the sales figure for the uppercase "JIM" rather than the lowercase "Jim" however it just extracts the first match it finds so returns £100 instead of £65.
5. Inserting a Column will Give Wrong Results
If you insert a column anywhere in the VLOOKUP table then the VLOOKUP formula in the worksheet will give an incorrect result. This is because the column number is hard coded in the formula.
In this example I have inserted a column between the Sales Person column and the Sales column. The VLOOKUP formula now returns a 0 because it is looking at the second column which is blank. You would have to manually change the third argument in the VLOOKUP formula from a 2 to a 3 to extract the sales figure for "Jim".
To overcome this problem you can make the third argument dynamic using the COLUMN function.
In this example I have changed col_index_num to COLUMN()-4. Now if I insert or delete columns between the Sales Person and Sales column the VLOOKUP function still returns the sales figure.
How You Can Overcome Some Limitations of VLOOKUP
You can eliminate the first two limitations by using the Advanced VLOOKUP Excel Add-in which contains two advanced VLOOKUP functions. The first will look at the columns to the left of the lookup column so will return values not only to the right but also to the left of the lookup value column. This will eliminate the need to manually change your worksheet which will save you time and effort.
The second advanced VLOOKUP function will extract more than one value if there are duplicate lookup values in the lookup table.
To learn more about how these two Advanced VLOOKUP functions work you can watch my video below where you can see them in action.
You can buy the Advanced Excel Add-in in my shop.
Excel Formulas and Functions: The Complete Excel Guide for Beginners Book
If you want to learn how to create VLOOKUP formulas then you can buy my book called Excel Formulas and Functions: The Complete Excel Guide for Beginners here. This book shows you all the best Excel functions to use for your spreadsheets including VLOOKUP, HLOOKUP, INDEX/MATCH and many many more.
BUY THE BOOK
I hope you enjoyed my post on the limitations of VLOOKUP. If you have any questions or feedback or if you think I have missed anything then please leave a comment below.