Hello Excellers and welcome back on this sunny evening to another #Excel #FormulaFriday blog post in my 2019 series. The VLOOKUP Excel formula or function 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 is an Excel tutorial to take you through some common issues.
VLOOKUP Formula Syntax Reminder
Before we move into my top 6 reasons for VLOOKUP not working let’s take a quick step back and have a quick refresher of the formula syntax.
=VLOOKUP (value, table, col_index, [range_lookup])
- value – The value to look for in the first column of a table.
- table – The table from which to retrieve a value.
- col_index – The column in the table from which to retrieve a value.
- range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.
Help!. My VLOOKUP Is Not Working!
So, I am going to show with you my top 6 reasons that a novice VLOOKUP user finds their formula is not doing what they expected.
1. The issue of the Left-Hand Column.
Vlookup can ONLY look right. In this way, it is quite restricting. A lot of users 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 Issue 1.
The solution to this involves not using the VLOOKUP function. But, 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.
=VLOOKUP(value,table_array,column_index_number,[range_lookup])
Most 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 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.
The Solution To Issue 2.
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. Using The Wrong Column.
The column_index_number part of the formula is the column from which the value is looking up is returned. This part of the formula is really not dynamic and can return an incorrect value if an extra column is 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.
The Solution To Issue 3.
So, there are a few actions you can take to ensure this does not happen.
- Lock the worksheet – this will prevent users from 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 example 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 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.
The Solution To Issue 4.
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.
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 couple of ways around this
The Solution To Issue 5.
- Convert your data source to an Excel table. Home Tab- Styles Group- Format As Table
- Always go to the data source and hit CTRL+A to get the full data set refreshed.
If possible I use the first method and convert the data source to an Excel table.
6. You Data Contains Duplicates.
The VLOOKUPExcel 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 analyzed for each date.
The Solution To issue 6.
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. If you want to want to read more about Pivot Tables feel free to read my blog posts below or check out my YouTube videos on this subject.
I hope you enjoyed this Excel tutorial on VLOOKUP mistakes.
If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below
