VLOOKUP is like a lightbulb going on when you ‘get them’ for most Excel users. There is an ah hah moment when they work, but when they do not, it really frustrating especially for rookie users- in particular if there is not a more experienced Excel user around. Whilst it is a great formula it is a bit sensitive and has its limitations but as long as you abide by the rules you should be fine.
Here are are my top 6 reasons that a novice VLOOKUP user finds their formula is not doing what they expected. Grrrrrrrrr………
1. The issue of the Left Hand Column. Vlookup can ONLY look right. In this ways it is quite restricting. A lot of user will copy and paste columns to accommodate this rule. It can be annoying but thems the rules!. So first simple mistake users make not ensuring the info you have (lookup-value) is in the first column of data you’re looking at (table array) and the information you want to return is to the right.
The solution to this involves not using the VLOOKUP function. Using a combination of the INDEX and MATCH functions of Excel is a common alternative to VLOOKUP. It is far more versatile and really opens up more potential in using Excel.
The example below shows Index Match being used to return information to the left of the column you are looking in.
2. You Need An Exact Match. The last part of the VLOOKUP formula ….requires you to specify an exact or an alternatively an approximate match.
Mostly Excel users look for an exact match and in those instances the value FALSE or zero needs to be entered into this part of the formula. If you leave it blank then the default value is TRUE, which means it will look for an approximate match. This is where the mistakes can be made.
There are numerous circumstances when you would look for an approximate match from a VLOOKUP, for example if you are looking at the sales commission bracket or banding – you can see my related blog post/article here for greater detail and explanation.
You can see in the example below that an incorrect value has been returned by the formula as the default of zero or TRUE was left in the range_lookup.
In order for the VLOOKUP to work the TRUE values must be sorted in ascending order. Let’s sort the data and this is the CORRECT result.
So if you are looking for an exact match- make sure you enter the value FALSE in range_lookup.
3. Wrong Column. The column_index_number part of the formula is the column from which the value are looking up is returned. This part of the formula is really not dynamic and can return incorrect value if an extra column in inserted into the worksheet in the area where your data is stored. You can see that demonstrated below. An extra column has been inserted and now our lookup column has moved to the right again giving us an incorrect value.
So, there are a few actions you can take to ensure this does not happen.
- Lock the worksheet – this will prevent users making changes. This is not always a viable solution if users do need to amend the worksheet.
- Use another function with your VLOOKUP- that is the MATCH function, insert this into the col_index_num part or argument of the formula.
You can see below the incorrect details have been returned in the exmaple as the wrong column (3 instead of 2) has been entered into the formula.
4. Dragging Formula Error. This mistake is the one that catches people out the most in my experience and it’s really easy to solve. See in our example below, the first few rows have returned the correct result, but as we have dragged the formula down the column, problems have occurred in the lookup formula. One good point is the formula has started to work, the only problem is the the table_array part of the formula has not been locked or made absolute so when the formula is dragged down the column, it does not move. In this case that is exactly what has happened. You can see in the example below the table has moved considerably as the formula has been dragged.
If we fix this issue by make the table_array absolute by wrapping it in $ and we have a solution.
5. Data Source Has Expanded- extra rows of data have been added. This is again one of the more common reasons I see. Simply there has been more data added to the data source and the table_array part of the formula has not been updated. There a a couple of ways around this
- Convert your data source to an Excel table. Home Tab- Styles Group- Format As Table
- Always go to data source and hit CTRL+A to get the full data set refreshed.
If possible I use the first method and use the data source as a table.
6. You Data Contains Duplicates. The VLOOKUP function can only return one record. It will return the first record that matches the value you looked for in your table array. If your data has more than one possible lookup value then VLOOKUP is not the function you need at this time. I suggest a Pivot Table is used as an alternative. In the example below if you have two entries for February 15th 2015 it will only return the first value that it comes across.
If you were to use a Pivot Table the number of calls taken can easily be analysed for each date.
This post explored the most common reasons for VLOOKUP not working. If you want more to answer any question using lookup formulas then I recommend Chandoo’s VLOOKUP Book. Click here to view more details
Want to Learn More Formulas?
Join the Excel Formulas Crash CourseIf you want to learn SUMIFS, SUMPRODUCT, OFFSET and 40 other day to day formulas, then consider Chandoo’s Excel Formula Crash Course.
It has 31 lessons split in to 6 modules and makes you awesome in Excel formulas.