VLOOKUP - The Reasons for NOT Using It and the Alternatives
Updated: Jan 22
Although undeniably popular and the first port of call for many Excel users when needing to look up associated data, users of the function should also be aware of its limitations and that there are more flexible alternatives out there.
The below article will detail the most common limitations that users may experience when using the function and what alternatives are available
Does Not Return Exact Match by Default
VLOOKUP has an optional parameter at the end of the function for [range_lookup). If this is not entered as FALSE (or 0) then Excel will assume that you want to do an approximate match.
In 99% of cases this will not be your intention and Excel will therefore return an incorrect value.
An illustration of the issue can be seen by using a short data sample below:
So if you were looking for the value of ‘10’ and wanted to return the associated value in the adjacent column then adding ‘FALSE’ or ‘0’ to the end of the VLOOKUP function would do this correctly (and would return ‘D’.)
However, by omitting the end parameter, Excel will assume that you want to return an approximate match. By doing so, it expects a sorted list and will treat any data as such, regardless of whether it is sorted or not.
It will then look for the first value that is higher than the value you are looking for and will return the value from the previous row.
So in the above example, the same formula would look like:
But as the FALSE parameter has been omitted, Excel will look for the first figure that is above 10 in the first column and then will return the value from the adjacent column on the previous row (assuming that this was the next closest match, not realising that the list has not been sorted and an exact match what was actually required.)
So in the above example the approximate match would return ‘A’ from the adjacent cell next to ‘3’. As the first number it found greater than the lookup value of 10 that was being searched for was 25, so it will return the value from the row above, as it is incorrectly assuming that 3 was the closest match to 10 in the unsorted list.
Therefore as this is the default setting, care must be taken to always ensure an exact match is returned where needed, to avoid unexpected results in your analysis and modelling work.
VLOOKUP Can Only Return Data to the Right
One of the most common frustrations that Excel users experience with VLOOKUPs are when they need to return data from the left of the data that they are looking up from.
As you need to specify a column to return, Excel requires the data to be structured in a way that your first column in a Vlookup is always the column where you want to lookup a value and then the column you want to return data from needs to be at least one column to the right of that (you specify an index of which column to return).
So in the above example you will see the data range is column C to D and we are instructing Excel to lookup from the first column (using cell C3 as the lookup value) and then return from column 2 (which is column D).
That would work fine, but let’s say the data you received was a little less structured and the data you wanted to return was in column B and not column D. As you are looking up from column C, in theory, your column index in the VLOOKUP would need to be -1 (minus 1) and the function does not allow that, as only positive column index numbers can be used.
This then results in the user having to manipulate data to restructure it for the function to work in that manner, which often is not practical or easy to do so.
There are workarounds to the issue, such as incorporating CHOOSE into your VLOOKUP, which would then allow you to do this, but then by having to complicate the function further this then adds further weight to the idea of moving to one of the alternatives detailed later on, as the syntax of these makes a task such as reading to the left a lot easier without the need for workarounds that over complicate the task.
Adding a Column to VLOOKUP Causes Errors
One of the most significant flaws of VLookup is that by simply adding a column into the data set that Vlookup is referring to, the function will then automatically return incorrect results.
Example before adding a column:
Example after adding a column
So, in the original example you will see that we are referring to a data set of columns C to D and instructed Excel to return the second column, which works as expected.
However, in the second example we’ve now inserted an additional column between C & D, which now causes the Vlookup to refer to columns C to E. So there is an additional column in the function, yet the column reference of ‘2’ remains unchanged and is static. This means that Excel is now referring to column D and hence why it is (incorrectly) returning zero, due to the lack of a dynamic column index.
This poses a serious risk to your Excel data and requires you to protect the structure from users changing the column structure. You could also attempt to mitigate things further by making the column Index relative (such as using COLUMN or in combination with MATCH), but again this is another pointer towards switching to less restrictive (and more stable!) options.
VLOOKUP Will Only Return the First Match Found
When returning values from a VLOOKUP. Excel will take a top down approach and will return the first match found based on the user’s criteria.
The issue is that there could be multiple instances of the match and you may want to return the 2nd value, 3rd value etc. and not just the first one.
VLOOKUP Is Not Case Sensitive
If you ever need to return data from a lookup that requires case sensitivity then you will hit a stumbling block with VLookup.
Vlookup will always return the first match found, ignoring case.
Finding the Returned Column in Vlookup Is Not Easy
If we consider the below formula, it will illustrate an issue with the difficulty in understanding what column the VLOOKUP is returning:
So, the formula is instructing Excel to return a column 14 columns away from column E (which would be the lookup column, which is included in the count of 15).
So to figure out what column is being returned you could either add 14 onto letter E and work through the alphabet (F =1, G =2 etc. which doesn't seem the safest option!), or cursor across 14 times to check that the value being returned matches with that column and you then can see what column that is returning from.
The above is hard work for something that we can easily find out with alternative lookup functions, therefore the lack of readability also lists as a limitation of the VLOOKUP function.
So now the limitations of VLOOKUP have been detailed above, two questions remain:
Why do so many users continue to use it?
What are the alternative options?
To answer the first point, I think VLOOKUP is popular because it may be deemed slightly easier to get to grips with than the alternative options, although I'm not too sure this is actually the case once you get used to the alternative way of working and the benefits it brings.
I still see job advertisements and CVs listed "Must have advanced Excel skills, including VLOOKUP', or the applicant indicating that they have those skills and I think this is the reason it is so popular, it just appears to be much more mainstream.... but there are better options!
For a small learning curve you can unlock some serious power and I believe that the investment in doing so will pay dividends in a short space of time.
Introducing the two alternatives that could completely remove the need for you to ever use a VLOOKUP again! (and an article on each which you can click through to!).
These functions will solve all of the above issues and provide you with a much more flexible and robust solution.
XLOOKUP* - The up and Coming Excel Lookup Function Ready to Take the Crown! (coming soon!)
* Note : At the time of writing this article, whilst XLOOKUP looks like it will be taking the lookup crown, it has not yet been released to all users of Excel and is currently in the process of being rolled out to users of the latest version.
XLOOKUP will not work on previous versions of Excel (and those who haven't got the latest Excel version following its release etc.) so for that reason (and also backwards compatibility) the age old lookup of solution of INDEX/MATCH is detailed below!
INDEX/MATCH has been around for a long time and will continue to be a formidable force in the lookup world until XLOOKUP becomes more established and the need for backwards compatibility is removed etc.
For more articles like this please sign up for notifications of future posts. You can also visit Insightful Dynamics at www.insightfuldynamics.co.uk or contact us here: email@example.com