We absolutely love Excel’s Vlookup formulae. It’s one of those wonderful pieces of spreadsheet wizardry that we return to time and time again. I can’t tell you how many times it’s rescued me from problems.
If like me you work with various data sets then it’s more than likely that at some point you will have to take information from one and look it up in another. Vlookup is your’ baby for that but of course, there are times when that doesn’t go to plan and the lookup function doesn’t appear to want to work properly.
So when that happens what do you do?
How to do Vlookup in excel/vlookup in excel with example
OK – let’s first look at what Vlookup is so you can understand the mechanics of how it functions. Then we’ll get into the nitty gritty of how it sometimes doesn’t seem to want to do what you want and what the common causes for this are.
If you’re an excel expert and just want to skip to the likely problems then you’ll find our top 5 reasons for vlookup failure towards the bottom of the article.
What Vlookup does
Vlookup is a function that looks up a particular value from a cell that you specify within another data range/table and returns a value from that lookup table based on the column number that you enter. It can find approximate or exact matches. Let’s look at an example
In the example above it’s a fairly simple table (5 columns and 5 rows of data), we’ve used the VLOOKUP formulae of =VLOOKUP(I6,C6:F11,3,FALSE).
If we deconstruct it then:
* I6 contains the data that we want to look up
* C6:F11 is the table array that contains the table we’re looking up
* 3 is the column we want the formulae to return the data from
* FALSE – this means that we want an exact match against I6
So in our example we’re looking up the value of 1ABC and when it’s found we want the result from 3 columns to the right of it – in this example 10.
So what can go wrong with VLOOKUP?
Many of us struggle with VLookups and find the formulae complex and difficult to understand, fortunately, excel has three ways of telling you there’s a fault
1/ ISERROR message
2/ #N/A message
3/ The wrong result (not always obvious!)
Now the first 2 of those are easily understood. Unfortunately, the latter needs to be spotted first and then investigated.
As a rule, whenever I use Vlookup I always sample check a few results because I always check a sample of results to ensure I’m getting what I want). Now don’t get me wrong, Vlookup is a formula and will work exactly how it’s supposed to so usually the issues are with how you’ve written the formula or the data table you’re looking for within. Our 5 common issues are listed below.
5 reasons why you might get the wrong result from your VLOOKUP formula
1/ What your looking for isn’t in the table – as maddening as you might find it, perhaps the value you’re looking up isn’t featured within the data set. Don’t believe me? Do a quick find (Ctrl + F) and look up what your trying to find, is it there? A common issue here is that the value is broken up by a space or perhaps has a prefix or suffix ahead of it.
2/ What your looking up is in the table but not in the column your searching for. Remember the lookup value must be in the leftmost column of the TABLE ARRAY that you’re using to search in, the formulae is constrained in this respect. Ie. if you’re searching for a value “ABC1” and it’s in column 2 of the table array you’ve entered you won’t derive the right result.
3/ You’ve copied the vlookup cell down and not locked the range. I see this lots of time when you drag and drop to copy and you’ve forgotten to lock the cell with the $ symbol. In our example to lock the table array we would’ve written the formulae thus:
=VLOOKUP(I6,$C$6:$F$11,3,FALSE).
Note the above only locks the table array and doesn’t look the range of the value being looked up.
4/ You’ve not included the range lookup criteria and you’ve got the closest match which might not be what you want. That FALSE statement at the end of the formulae is *crucial* if you want an exact match, failure to include it my screw your results and deliver an approximate match.
5/ You’ve got the column reference incorrect that you want to derive the results from. In our example, we’re deriving the results from Column 3. Note this is the column number in the table array. So if we’d wanted the value in our example we’d have used “4”, or if we’d wanted the description we’d used “2”. Note that another common similar issue is where you want the information that is to be returned that is to the left of the value you’re looking up. Vlookup will not function in that way and the results column *has* to be to the right of the lookup value.
In my opinion, most errors occur because the table array selected is not configured correctly for what’s being searched for. it’s always doing a few manual lookups (can you find the value your’ searching for, is what you want to be returned consistently in the right column and complete etc. Once you’ve proved out the process you can leave Vlookup alone to do its thing leaving you confident that you’ll get the right results every time.
Hope you found this useful. Got some other ideas on problem finding Vlookup that aren’t covered here? Feel free to use the comments section below.